cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Super User
Super User

Re: Combine columns if not null or empty

Hi,

 

There should be a solution to that as well.  Please take a practical example and show the expected result.

comecome Regular Visitor
Regular Visitor

Re: Combine columns if not null or empty

Here you have. It is the same than the previous one but just using words of different lenght in the rows. Before I only used characters of lenght 1...

 

CapturaExcel.JPG

 

Highlighted
Super User
Super User

Re: Combine columns if not null or empty

Hi,

 

Try this M code

 

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name1", type text}, {"Name2", type text}, {"Name3", type text}, {"Name4", type text}}),
    #"Inserted Merged Column" = Table.AddColumn(#"Changed Type", "Merged", each Text.Combine({[Name1], [Name2], [Name3], [Name4]}, ";"), type text),
    #"Trimmed Text" = Table.TransformColumns(#"Inserted Merged Column",{{"Merged", Text.Trim}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Trimmed Text", "Merged", Splitter.SplitTextByEachDelimiter({";"}, QuoteStyle.Csv, false), {"Merged.1", "Merged.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Merged.1", type text}, {"Merged.2", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Merged.2"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Merged.1", "Result"}})
in
    #"Renamed Columns"

 

Hope this helps.

 

Untitled.png

comecome Regular Visitor
Regular Visitor

Re: Combine columns if not null or empty

Of course it helps! It works! I shows me the power of the delimeters! Thank you very much!

Super User
Super User

Re: Combine columns if not null or empty

Hi,

 

You are welcome.  Please mark my reply as Answer.

comecome Regular Visitor
Regular Visitor

Re: Combine columns if not null or empty

I cannot see where to mark your reply as an answer..  In the place  where there are 3 points in a row there is not such option in the scroll down menu... maybe  it's only allowed just one "solved" per post (thread)

maddy Regular Visitor
Regular Visitor

Re: Combine columns if not null or empty

@MarcelBeug 

 

Do u know how to achieve the same result in DAX. I also go the same question. Since i am using direct query I cannot do any transormations using M query.

 

Regards,

Maddy