# Merge Column - ignore blanks Power Query



## GorD (Aug 19, 2016)

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 (Aug 19, 2016)

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 (Aug 19, 2016)

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 (Aug 19, 2016)

GorD said:


> 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 (Aug 20, 2016)

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 (Aug 20, 2016)

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 (Aug 20, 2016)

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 (Aug 21, 2016)

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 (Aug 21, 2016)

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


----------



## ImkeF (Aug 21, 2016)

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
```


----------



## GorD (Aug 19, 2016)

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 (Aug 21, 2016)

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 (Aug 21, 2016)

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 (Aug 25, 2016)

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


----------

