Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
tondeli
Advocate I
Advocate I

Merge columns between column values, dynamic count of columns

In my data I have id-key, dimension and dimension value. Three columns total. I don't know how many different values dimension column holds (0,1,2...n). I have pivoted my data so that dimensions will be in columns. Next step is that I need to merge dimension values as one columns.

 

My data after pivot-operation:

id-key 1 2 3 4 5 0 Merged id001 a 3 aa true a - 3 - aa - - - true id002 5 rh zz false - 5 - rh - zz - - false

My problem is that I'm not sure how many columns I will have in this point. So, the following doesn't work.

 

= Table.AddColumn(#"Pivoted Column", "Merged", each Text.Combine({[1], [2], [3], [4], [5], [0]}, " - "), type text)

I'd like to use solution something like this:

 

= Table.AddColumn(#"Pivoted Column", "Merged", each Text.Combine({between([1], [0])}, " - "), type text)

 

All ideas are welcome!

1 ACCEPTED SOLUTION

A dynamic solution:

 

    #"Added Custom" = 
        Table.AddColumn(
            #"Pivoted Column",
            "Merged", 
            each Text.Combine(
                List.Transform(
                    List.Skip(
                        Record.FieldValues(_)), 
                    each Replacer.ReplaceValue(
                        Text.From(_),
                        null,
                        "")),
                " - "),
            type text)
Specializing in Power Query Formula Language (M)

View solution in original post

9 REPLIES 9
tondeli
Advocate I
Advocate I

My ideas was to start with:

 

Table.ColumnNames

  • Creates list of all column names in table --> Not sure how to name my table. Is it the last step in my QueryEditor?

List.Generate

  • Creates list based on values --> I need to create min and max value from my dimension column

Text.Combine

  • List of text to combine --> Not sure if I'll need this step at all...

 

Haven't got any of these to work yet...

Hi @tondeli,

As I tested, please select all columns(no matter how many columns you have) by clicking "CTRL+A", then right click->Merge Columns->type the custom space as picture2 shown.

Picture1Picture1     Picture2Picture2
You will get expected result as follows.

resultresult
It's easier to merge columns by interface navigation, rather than type the Query statement. After operation in interface, the Power Query statement will be generated automatically, you can click "Advanced Editor" and view the statements.

Best Regards,
Angelia

This solution gives me the following query line:

 

= Table.CombineColumns(#"Pivoted Column",{"id-key", "1", "2", "3", "4", "5", "0"},Combiner.CombineTextByDelimiter(" - ", QuoteStyle.None),"Merged")

It still "hard codes" those column names. If I remove one column this step gives arror. If I add new column this step skips that column in this step.

 

Result is ok, but I'd like to refer dynamic amount of columns in my data.

Just to be clear:

you didn't react to my solution as this is dynamic.

Specializing in Power Query Formula Language (M)

Hello @MarcelBeug,

 

I have a similar situation and I was wondering if you could help me with this.

 

I have the attached raw data and desired result here - https://drive.google.com/open?id=1fNQePPTGmQ8dDXTsQFwsSS6UMPb4Sss1

 

The output needs to be generated in two columns where Combiner.CombineTextByDelimiter will ignore the null values and if there is a null value there will be no delimiter.

 

I have worked on this by following code. But off course could not generate the output as I desired. If I could have worked the code as desired, at final step I would split the table on the first delimiter and I would have the output.

 

let
    Source = Excel.Workbook(File.Contents("C:\Users\smpa01\Documents\PBIX\Dynamic Merge Test.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Sheet1_Sheet,{{"Column1", type text}, {"Column2", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Name", type text}, {"Country", type text}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type1",{{"Name", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Name"}, {{"Count", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count],"X",1,1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Count"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Name", "Country", "X"}, {"Custom.Name", "Custom.Country", "Custom.X"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Expanded Custom",{"Name"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Removed Columns1", {{"Custom.X", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Removed Columns1", {{"Custom.X", type text}}, "en-US")[Custom.X]), "Custom.X", "Custom.Country"),
    #"Replaced Value" = Table.ReplaceValue(#"Pivoted Column",null,"",Replacer.ReplaceValue,{"Custom.Name", "1", "2", "3", "4", "5", "6"}),
    Custom1 = Table.ToList(#"Replaced Value", Combiner.CombineTextByDelimiter(",")),
    #"Converted to Table" = Table.FromList(Custom1, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    #"Converted to Table"

Thank you in advance

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

Found it - https://community.powerbi.com/t5/Desktop/Combine-columns-if-not-null-or-empty/td-p/187969

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

A dynamic solution:

 

    #"Added Custom" = 
        Table.AddColumn(
            #"Pivoted Column",
            "Merged", 
            each Text.Combine(
                List.Transform(
                    List.Skip(
                        Record.FieldValues(_)), 
                    each Replacer.ReplaceValue(
                        Text.From(_),
                        null,
                        "")),
                " - "),
            type text)
Specializing in Power Query Formula Language (M)
Anonymous
Not applicable

@MarcelBeug Could you explain what the parts in your function does? I like the result but have a hard time adjusting it for my needs.

Best regards,

Alex

This is awesome.

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.