Forum Discussion

tondeli's avatar
tondeli
Icon for Advocate I rankAdvocate I
8 years ago
Solved

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...
Need Help
  • MarcelBeug's avatar
    MarcelBeug
    8 years ago

    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)
v-huizhn-msft's avatar
v-huizhn-msft
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

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.

Picture1     Picture2
You will get expected result as follows.

result
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's avatar
MarcelBeug
Icon for Community Champion rankCommunity Champion
8 years ago

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)
  • smpa01's avatar
    smpa01
    Icon for Community Champion rankCommunity Champion
    8 years ago

    This is awesome.

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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