Merge Column - ignore blanks Power Query

[GorD]

Hi there, Our MRP system has 20 notes fields available where 2 digit codes can be entered against a part, typically between 2 and 10 will have a code entered, but my issue is that aren't always entered in the same field. ( example below codes are entered in notes field 1,2 and 7)

I am trying to get a merged list showing all the codes against each part, without getting something like AG,AN,,,,WE,,,,,,,,,,,,,,.

I think if the cells without a code entered where genuine blanks (null) they may be ignored in the merge but as they are being pulled from a table in another file they do have a formula in them.

How can I get above to show AG,AN,WE

Many thanks

 

[ImkeF]

Actually, the non-nested function equivalent wasn't correct above, as it adds more columns. This is more like it:

let
    Source = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
    ListOfAllValuesInTheCurrentRow = Table.AddColumn(Source, "Result", each Record.FieldValues(_)),
    FilterTextLength2 = Table.TransformColumns(ListOfAllValuesInTheCurrentRow,{{"Result", each List.Select(_, each Text.Length(_)=2 )}}),
    Result = Table.TransformColumns(FilterTextLength2, {{ "Result", each Text.Combine(_, ", ") }} )
in
    Result

looking better already

But still ... I think we need sth like the DAX-formatter which structures the code without adding unnecessary steps. (Maybe they ship that with the syntax-highlighting ? )

 

[ImkeF]

We're getting closer

let
    Source = #table({"Col1", "Col2", "Col3"}, {{"AK", "", "BC"}, {"LK", "WD", null}}),
    
    Result= Table.AddColumn(Source, "Result", each let  
                                                      s1 = Record.FieldValues (_),
                                                      s2 = List.Select (s1, each Text.Length(_)=2),
                                                      s3 = Text.Combine (s2, ", ") 
                                                   in s3
                            )

in
    Result

 

[GorD]

Wow, I forgot about this thread and missed all this discussion, which to be honest is way over my head anyway, but interesting all the same.
ImkeF - How do I use the code - do I just paste it into the Advanced editor? Sorry for such a basic question. Which version is best to use ?

Thanks to both

Matt you were also correct in that I only needed to start at 6 commas, although i do need to leave 1 comma between codes