cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tondeli Regular Visitor
Regular Visitor

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

Accepted Solutions
MarcelBeug Super Contributor
Super Contributor

Re: Merge columns between column values, dynamic count of columns

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)
9 REPLIES 9
tondeli Regular Visitor
Regular Visitor

Re: Merge columns between column values, dynamic count of columns

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

v-huizhn-msft Super Contributor
Super Contributor

Re: Merge columns between column values, dynamic count of columns

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.

1.PNGPicture1     3.pngPicture2
You will get expected result as follows.

2.PNGresult
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

MarcelBeug Super Contributor
Super Contributor

Re: Merge columns between column values, dynamic count of columns

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)
tondeli Regular Visitor
Regular Visitor

Re: Merge columns between column values, dynamic count of columns

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.

MarcelBeug Super Contributor
Super Contributor

Re: Merge columns between column values, dynamic count of columns

Just to be clear:

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

Specializing in Power Query Formula Language (M)
smpa01 Established Member
Established Member

Re: Merge columns between column values, dynamic count of columns

This is awesome.

smpa01 Established Member
Established Member

Re: Merge columns between column values, dynamic count of columns

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

smpa01 Established Member
Established Member

Re: Merge columns between column values, dynamic count of columns

Highlighted
Flyckten Frequent Visitor
Frequent Visitor

Re: Merge columns between column values, dynamic count of columns

@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

Helpful resources

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.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 339 members 3,721 guests
Please welcome our newest community members: