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

 

[Matt Allington]

I'm sure there are lots of solutions. One way would be to merge all the columns, then do a replace ",,,,," with null, followed by replace ",,,," with null etc. Could that work?

 

[GorD]

Hi Matt and thanks for the reply. Yes i think it would work, but if only 2 codes are entered from 20, worst case I would have to start at 18 commas and work my way down, but i think it will work if I can't find another way

Thanks again

 

[Matt Allington]

but if only 2 codes are entered from 20, worst case I would have to start at 18 commas and work my way down

Not really. The steps are additive. So if you worked through 6,5,4,3,2 then it will clean out a maximum of 20 extra commas

 

[ImkeF]

Hi there,
this little monster will combine only those fields, who have a text-length of 2:

let
    Source = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
    Index = Table.AddIndexColumn(Source, "Index", 0, 1),
    #"Added Custom" = Table.AddColumn(Index, "Custom", each Text.Combine(List.Select(Record.FieldValues(Source{[Index]}), each Text.Length(_)=2), ", "))
in
    #"Added Custom"

 

[Matt Allington]

Wow, i learnt a lot from that! BTW, I had to remove the space after the comma in the last line to make it work. Like this
Text.Length(_)=2), ",")

I'm still waiting for Chris Webb to write that book about PQL. In the meantime, where can I learn about this stuff?

 

[Matt Allington]

In fact I would go further than saying "I learnt a lot". This has actually been a game changer for me. The pieces have finally fallen in place for me and I have a new broad understanding of what I should be trying to do with PQL.

 

[ImkeF]

Hi Matt,
glad that you see light as well

I learned like you just now: Apart from using it in my consultancy work, I expanded my practice by trying to solve questions in the forums. There I had my light-bulb-moments when Bill Szysz presented his alternative solutions

This is very effective as you know - as it is basically the same principle that underlies your DAX-book, isn't it? : First try by yourself and then study how a really smart solution looks like. Yes, a good theory-book would help (to different extends), but all it nothing without practice

The key to these advanced formulas lies in understanding the input- & output-formats of the formulas: Which formula can give me the desired result and if it takes a different format than my data currently is in, transform it into that shape. So to understand your tools (formulas) you not only have to learn what they do (combine, split...) but just as important: Which format this formula expects as input and which format it is returning.

 

[ImkeF]

My collection of current resources to learn M as a language can be found on my website here: Learning resources – The BIccountant

 

[ImkeF]

I knew there must have been a shorter way to do it, but couldn't remember. But here it is:

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

So we can retrieve the content of all columns in the current row just by using "_".
No need to use create an index-column then just for referencing the needed row.

The following query does just the same, just that it doesn't nest the functions. As I learned, this seems to be the way proper coding is expected to look like (from the professional coders...) Wondering if this is actually also preferable for Excel-users, as we are used to read this nested stuff. At least at the beginning I always felt more intimidated by multiple rows of code than with nested. What do you think?

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