Pivot or Unpivot Multiple Columns

[psbrown]

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?

[Ashish_Mathur · Accepted solution]

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"

[Anonymous]

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.

[psbrown]

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}}),

[psbrown]

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 · Accepted solution]

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 · Accepted solution]

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"),

[Ashish_Mathur]

You are welcome.  The partition step is like the COUNTIF() function of MS Excel.  It assignes a diffeent number to each repeat instance of a ClientID.  I should have named that datacolumns instead.  This step is written by hand and returns the column names as a list. 

[psbrown]

Thank you again! I see I still have a lot to learn.

[Ashish_Mathur]

You are welcome.

[Ashish_Mathur]

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.