Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
I got an excel sheet that looks like this and want to make it look like the one below using PowerQuery... I've tried the unpivot function but it behavious in a funny way. Help is appreciated.
Category | Event | Date | Event | Date | Event | Date | Event | Date | Event | Date | Event | Date | Event | Date | Event | Date | Event | Date |
AAA | A | 25/5/2018 | B | 27/5/2018 | C | 4/6/2018 | D | 21/7/2018 | R | 22/8/2018 | F | 20/12/2018 | G | 18/2/2019 | H | 10/3/2019 | I | 19/4/2019 |
BBB | J | 16/6/2018 | K | 18/6/2018 | L | 26/6/2018 | M | 12/8/2018 | N | 13/9/2018 | O | 11/1/2019 | P | 12/3/2019 | Q | 1/4/2019 | S | 11/5/2019 |
Category | Event | Date |
AAA | A | 25/5/2018 |
AAA | B | 27/5/2018 |
AAA | C | 4/6/2018 |
AAA | D | 21/7/2018 |
AAA | R | 22/8/2018 |
AAA | F | 20/12/2018 |
AAA | G | 18/2/2019 |
AAA | H | 10/3/2019 |
AAA | I | 19/4/2019 |
BBB | J | 16/6/2018 |
BBB | K | 18/6/2018 |
BBB | L | 26/6/2018 |
BBB | M | 12/8/2018 |
BBB | N | 13/9/2018 |
BBB | O | 11/1/2019 |
BBB | P | 12/3/2019 |
BBB | Q | 1/4/2019 |
BBB | S | 11/5/2019 |
Thanks,
Dat_Data
Solved! Go to Solution.
Unpivot would do the job if the Event names were in the column headers. With this data structure there will be no quick and easy solution I'm afraid
If the data is coming from an Excel pivot, why not just rearrange it in Excel?
Proud to be a Datanaut!
Unpivot would do the job if the Event names were in the column headers. With this data structure there will be no quick and easy solution I'm afraid
If the data is coming from an Excel pivot, why not just rearrange it in Excel?
Proud to be a Datanaut!
I accept your post as a solution since i agree there isnt an easy way to make this work...however, one thing that just might work (but is not a good time investment) is to chop the table into several ones via DAX and then append the tables together...you're right, ill just do it through excel 🙂
Thanks,
Dat_Data
@Anonymous @Stachu
Pardon my intrusion. There is a little trick which might work (I think I learnt from one of @parry2k 's solutions which used Number.Mod)
I am attaching the Excel file. See if this useful.
It works with the sample data and mostly done using Query Editor's interface
let Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text},
{"Column3", type any}, {"Column4", type text}, {"Column5", type any}, {"Column6", type text}, {"Column7", type any}, {"Column8", type text}, {"Column9", type any}, {"Column10", type text}, {"Column11", type any}, {"Column12", type text}, {"Column13", type any}, {"Column14", type text}, {"Column15", type any}, {"Column16", type text}, {"Column17", type any}, {"Column18", type text}, {"Column19", type any}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Column1"}, "Attribute", "Value"), #"Filtered Rows" = Table.SelectRows(#"Unpivoted Columns", each ([Column1] <> "Category")), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Attribute"}), #"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 0, 1), #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each [Index]-Number.Mod([Index],Table.RowCount(Source)-1)), #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Index"}), #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns1",{"Custom", "Column1", "Value"}), #"Grouped Rows" = Table.Group(#"Reordered Columns", {"Custom", "Column1"}, {{"All", each _[Value]}}), #"Removed Columns2" = Table.RemoveColumns(#"Grouped Rows",{"Custom"}), #"Added Custom1" = Table.AddColumn(#"Removed Columns2", "Custom", each Text.Combine(List.Transform([All],each Text.From(_)),"|")), #"Removed Columns3" = Table.RemoveColumns(#"Added Custom1",{"All"}), #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns3", "Custom", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Custom.1", "Custom.2"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom.1", type text}, {"Custom.2", type datetime}}) in #"Changed Type1"
oh absolutely, you can also do it with PowerQuery/M, not only DAX, but I honestly don't think it's worth the time
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.