cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
niark Frequent Visitor
Frequent Visitor

Combine columns if not null or empty

Hi there,

 

I have some difficulties to combine text columns  with the result I want. If someone could help me to point me to the right direction Smiley Happy

 

So I have this table :

Col1         Col2       Col3      Col4       Std Combine        Wanted

ABCDA & B & C & DA & B & C & D
 B  & B & &B
A C A & & C &A & C
A   A & & &A
 B D& B & & DB & D

 

if I combine the columns with the std feature , you can see that empty ones are surrounded by unnecessary "&" char.

what I would like is the "Wanted" column result.

 

My ideas were to create a list of not empty columns and combine them but I'm not good enough to create a custom function for that...

 

Thanks.

2 ACCEPTED SOLUTIONS

Accepted Solutions
niark Frequent Visitor
Frequent Visitor

Re: Combine columns if not null or empty

Good catch Marcel but I have other columns Smiley Happy

 

Thanks for your help, you pointed me to the right way !

 

here the solution :

= Table.AddColumn(#"Reordered Columns", "Personnalisé", each Text.Combine(List.Select(Record.FieldValues(Record.FromList({[Col1],[Col2],[Col3]}, type [Col1 = text,Col2 = text,Col3 = text])), each _<> "" and _ <> null)," & "))

 

Thanks again

View solution in original post

MarcelBeug Super Contributor
Super Contributor

Re: Combine columns if not null or empty

Pleased you solved your own issue.

 

I'd rather had shortened the code a bit:

 

= Text.Combine(List.Select({[Col1],[Col2],[Col3],[Col4]}, each _<> "" and _ <> null)," & ")
Specializing in Power Query Formula Language (M)

View solution in original post

16 REPLIES 16
MarcelBeug Super Contributor
Super Contributor

Re: Combine columns if not null or empty

If you have no other columns, you use the query editor to add a custom column with formula:

 

= Text.Combine(List.Select(Record.FieldValues(_), each _<> "" and _ <> null)," & ")

 

Text combine.png

Specializing in Power Query Formula Language (M)
niark Frequent Visitor
Frequent Visitor

Re: Combine columns if not null or empty

Good catch Marcel but I have other columns Smiley Happy

 

Thanks for your help, you pointed me to the right way !

 

here the solution :

= Table.AddColumn(#"Reordered Columns", "Personnalisé", each Text.Combine(List.Select(Record.FieldValues(Record.FromList({[Col1],[Col2],[Col3]}, type [Col1 = text,Col2 = text,Col3 = text])), each _<> "" and _ <> null)," & "))

 

Thanks again

View solution in original post

MarcelBeug Super Contributor
Super Contributor

Re: Combine columns if not null or empty

Pleased you solved your own issue.

 

I'd rather had shortened the code a bit:

 

= Text.Combine(List.Select({[Col1],[Col2],[Col3],[Col4]}, each _<> "" and _ <> null)," & ")
Specializing in Power Query Formula Language (M)

View solution in original post

niark Frequent Visitor
Frequent Visitor

Re: Combine columns if not null or empty

Yes you are right. I'm frustrated how it seems so easy Smiley Happy

 

Anonymous
Not applicable

Re: Combine columns if not null or empty

Hello MacelBeug

 

Would you be so kind, if possible, to give me a hint to solve my problem. It is very close to the one you solved here. There's no concatenation, and it is very close to the problem of union of sets in Set Theory...

 

There are sometimes I get the value for a key column using some join. Sometimes you look in serveral sources to get the values. In some sources you get the value but not in others.... At the end you only wish a column with a key value

Well I believe the best is to show a sample. I would like to get the red values in "column e"
Thank you very much in advance

 excelquery.jpg

Highlighted
Super User
Super User

Re: Combine columns if not null or empty

Hi,

 

Here is the M code i used

 

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Text1", type text}, {"Text2", type text}, {"Text3", type text}, {"Text4", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",null,"",Replacer.ReplaceValue,{"Text1", "Text2", "Text3", "Text4"}),
    #"Inserted Merged Column" = Table.AddColumn(#"Replaced Value", "Merged", each Text.Combine({[Text1], [Text2], [Text3], [Text4]}, ""), type text),
    #"Split Column by Position" = Table.SplitColumn(#"Inserted Merged Column", "Merged", Splitter.SplitTextByPositions({0, 1}, false), {"Merged.1", "Merged.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"Merged.1", type text}, {"Merged.2", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Merged.2"})
in
    #"Removed Columns"

 

Untitled.png

Anonymous
Not applicable

Re: Combine columns if not null or empty

Thank you very  much! It works fine! I'm trying to learn M, and this a is a very good and really useful example!

Super User
Super User

Re: Combine columns if not null or empty

You are welcome.  If my reply helped, please mark it as Answer.

Anonymous
Not applicable

Re: Combine columns if not null or empty

I have run yor M code and it works, but when I analyzed the code I realized you took advantange that every cell with data in my table has lenght just one (it just for a brief example) and you split column by position. In this case position "1" always works.

 

Imagine that in the cells of a certain row were just a word like "Luke",  now the split position  would be the length of the word "Luke". In the other rows could be another different length...

 

I suppose that an algorithm following your idea would be: calculate the maximum of the lenghts of each items on each row, maybe you have to use a dummy column with a value to concatenate further....  or maybe just a different approach to solve the problem!

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 138 members 1,590 guests
Please welcome our newest community members: