Pivot or Unpivot Multiple Columns

Hi, I have been trying to work on this myself for over a week and can't seem to figure it out. I have a dataset similar in format to the example below:

I would like to be able to display all values and dates for a given ID on one row like this:

I've tried PIVOT and UNPIVOT in Power BI - Power Query and can't get it in this format. Any ideas?

Need Help

Ashish_Mathur

3 years ago

Hi,

Here's a better solution. Even if the number of Data/Value combinations per client increase/decrease, the formatting would get applied automatically.

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ClientID", Int64.Type}, {"Value", Int64.Type}, {"Date", type date}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"ClientID"}, "Attribute", "Value.1"),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Unpivoted Other Columns", {{"ClientID", type text}}, "en-IN"),{"ClientID", "Attribute"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Merged"),
    Partition = Table.Group(#"Merged Columns", {"Merged"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Value.1", "Index"}, {"Value.1", "Index"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Partition",{{"Index", Order.Ascending}, {"Merged", Order.Ascending}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Sorted Rows", "Merged", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"ClientID", "Merged.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"ClientID", Int64.Type}, {"Merged.2", type text}}),
    #"Merged Columns1" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type1", {{"Index", type text}}, "en-IN"),{"Merged.2", "Index"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),
    #"Pivoted Column" = Table.Pivot(#"Merged Columns1", List.Distinct(#"Merged Columns1"[Merged]), "Merged", "Value.1"),
    datecolumnnames = Table.ColumnNames(#"Pivoted Column"),
    #"Converted to Table" = Table.FromList(datecolumnnames, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Custom" = Table.AddColumn(#"Converted to Table", "Custom", each if [Column1]="ClientID" then "text" else if Text.Start([Column1],4)="Date" then "date" else "number"),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Value.Type(if [Custom]="date" then #date(2023,1,1) else if [Custom]="number" then 1 else "A")),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Custom"}),
    Custom1 = List.Zip({#"Removed Columns"[Column1],#"Removed Columns"[Custom.1]}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Pivoted Column",Custom1)
in
    #"Changed Type2"

psbrown
3 years ago
Thank you! Both of your solutions worked very well and was easy for me to incorporate. For my own learning and understanding what is the Partition step and what did you use for that step? I've never used Partition and I am not finding it in the ribbon options. Also, how did you create the datecolumnnames? Was that custom code or a ribbon option?

Partition = Table.Group(#"Merged Columns", {"Merged"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),

datecolumnnames = Table.ColumnNames(#"Pivoted Column"),

9 Replies

psbrown
Frequent Visitor
3 years ago
I've added a table with sample data below. I tried to attach an XLSX and CSV version that could be imported into Power BI but I received a message it was not supported or I didn't attach it correctly.

ClientID Value Date
1 4 1/1/2023
1 5 2/5/2023
2 6 1/12/2023
2 4 2/8/2023
2 5 3/17/2023
2 6 4/14/2023
3 4 1/5/2023
3 5 2/18/2023
3 6 2/28/2023
4 4 3/5/2023
4 5 4/15/2023
- Ashish_Mathur
  Super User
  3 years ago
  Hi,
  
  This M code works
  
  let
  Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
  #"Changed Type" = Table.TransformColumnTypes(Source,{{"ClientID", Int64.Type}, {"Value", Int64.Type}, {"Date", type date}}),
  #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"ClientID"}, "Attribute", "Value.1"),
  #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Unpivoted Other Columns", {{"ClientID", type text}}, "en-IN"),{"ClientID", "Attribute"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Merged"),
  Partition = Table.Group(#"Merged Columns", {"Merged"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
  #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Value.1", "Index"}, {"Value.1", "Index"}),
  #"Sorted Rows" = Table.Sort(#"Expanded Partition",{{"Index", Order.Ascending}, {"Merged", Order.Ascending}}),
  #"Split Column by Delimiter" = Table.SplitColumn(#"Sorted Rows", "Merged", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"ClientID", "Merged.2"}),
  #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"ClientID", Int64.Type}, {"Merged.2", type text}}),
  #"Merged Columns1" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type1", {{"Index", type text}}, "en-IN"),{"Merged.2", "Index"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),
  #"Pivoted Column" = Table.Pivot(#"Merged Columns1", List.Distinct(#"Merged Columns1"[Merged]), "Merged", "Value.1"),
  #"Changed Type2" = Table.TransformColumnTypes(#"Pivoted Column",{{"ClientID", Int64.Type}, {"Date1", type date}, {"Value1", Int64.Type}, {"Date2", type date}, {"Value2", Int64.Type}, {"Date3", type date}, {"Value3", Int64.Type}, {"Date4", type date}, {"Value4", Int64.Type}})
  in
  #"Changed Type2"
  
  Hope this helps.
- Ashish_Mathur
  Super User
  3 years ago
  Hi,
  
  Here's a better solution. Even if the number of Data/Value combinations per client increase/decrease, the formatting would get applied automatically.
  
  let Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"ClientID", Int64.Type}, {"Value", Int64.Type}, {"Date", type date}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"ClientID"}, "Attribute", "Value.1"), #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Unpivoted Other Columns", {{"ClientID", type text}}, "en-IN"),{"ClientID", "Attribute"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"Merged"), Partition = Table.Group(#"Merged Columns", {"Merged"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}), #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Value.1", "Index"}, {"Value.1", "Index"}), #"Sorted Rows" = Table.Sort(#"Expanded Partition",{{"Index", Order.Ascending}, {"Merged", Order.Ascending}}), #"Split Column by Delimiter" = Table.SplitColumn(#"Sorted Rows", "Merged", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"ClientID", "Merged.2"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"ClientID", Int64.Type}, {"Merged.2", type text}}), #"Merged Columns1" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type1", {{"Index", type text}}, "en-IN"),{"Merged.2", "Index"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"), #"Pivoted Column" = Table.Pivot(#"Merged Columns1", List.Distinct(#"Merged Columns1"[Merged]), "Merged", "Value.1"), datecolumnnames = Table.ColumnNames(#"Pivoted Column"), #"Converted to Table" = Table.FromList(datecolumnnames, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Added Custom" = Table.AddColumn(#"Converted to Table", "Custom", each if [Column1]="ClientID" then "text" else if Text.Start([Column1],4)="Date" then "date" else "number"), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Value.Type(if [Custom]="date" then #date(2023,1,1) else if [Custom]="number" then 1 else "A")), #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Custom"}), Custom1 = List.Zip({#"Removed Columns"[Column1],#"Removed Columns"[Custom.1]}), #"Changed Type2" = Table.TransformColumnTypes(#"Pivoted Column",Custom1) in #"Changed Type2"
  - psbrown
    Frequent Visitor
    3 years ago
    Thank you! Both of your solutions worked very well and was easy for me to incorporate. For my own learning and understanding what is the Partition step and what did you use for that step? I've never used Partition and I am not finding it in the ribbon options. Also, how did you create the datecolumnnames? Was that custom code or a ribbon option?
    
    Partition = Table.Group(#"Merged Columns", {"Merged"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
    
    datecolumnnames = Table.ColumnNames(#"Pivoted Column"),

Anonymous

Not applicable

3 years ago

Hi psbrown ,

Click "transform data" to enter the power query editor, open "Advanced Editor" and copy and paste the following code. You can check the steps in the step column on the right.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc5LDsAgCATQu7A2oXy03sV4/2sUxVZJF2xeYJjWgCCB2hAS8sUCPTlmG8a8kQ2Kb3JUnas14rgXpPsfoEi6Vb4COaIXoBq1TOVDdQXIGaArwH692h8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ClientID = _t, Value = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ClientID", Int64.Type}, {"Value", type text}, {"Date", type text}}),
    #"Merged Columns" = Table.CombineColumns(#"Changed Type",{"Value", "Date"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged"),
    Custom1 = Table.Group(#"Merged Columns", {"ClientID"}, {{"Data", each Table.AddIndexColumn(_, "Index", 1, 1), type table}}),
    #"Expanded Data" = Table.ExpandTableColumn(Custom1, "Data", {"Merged", "Index"}, {"Merged", "Index"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Expanded Data", {{"Index", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Expanded Data", {{"Index", type text}}, "en-US")[Index]), "Index", "Merged"),
    #"Merged Columns1" = Table.CombineColumns(#"Pivoted Column",{"1", "2", "3", "4"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Merged Columns1", "Merged", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Merged.1", "Merged.2", "Merged.3", "Merged.4", "Merged.5", "Merged.6", "Merged.7", "Merged.8"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Merged.1", Int64.Type}, {"Merged.2", type date}, {"Merged.3", Int64.Type}, {"Merged.4", type date}, {"Merged.5", Int64.Type}, {"Merged.6", type date}, {"Merged.7", Int64.Type}, {"Merged.8", type date}})
in
    #"Changed Type1"

Please refer to my pbix file.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. Thank you in advance.

Pivot or Unpivot Multiple Columns.pbix80 KB

psbrown
Frequent Visitor
3 years ago
Thanks! I got it to work with a few minor tweaks on my dashboard. Can you explain to me which tranformation you used for custom1? I see it says Table.Group but I am not familiar and not sure I would know how to duplicate that step myself.

Custom1 = Table.Group(#"Merged Columns", {"ClientID"}, {{"Data", each Table.AddIndexColumn(_, "Index", 1, 1), type table}}),

Forum Discussion

Pivot or Unpivot Multiple Columns

9 Replies

Related content

Unpivot Column

Advanced unpivoting multiple columns

Unpivot multiple columns

Unpivot Multiple Columns

Unpivot columns

ClientID	Value	Date
1	4	1/1/2023
1	5	2/5/2023
2	6	1/12/2023
2	4	2/8/2023
2	5	3/17/2023
2	6	4/14/2023
3	4	1/5/2023
3	5	2/18/2023
3	6	2/28/2023
4	4	3/5/2023
4	5	4/15/2023