Forum Discussion
Unpivot for changing table
Hi guys,
I have a table that look something like this:
| Company ID | Report1 | Income | Expensses | profit | Report2 | Income | Expensses | profit | Report3 | Income | Expensses | profit |
| 11111 | 01/01/2021 | 10 | 5 | 5 | 01/01/2022 | 15 | 10 | 5 | 01/01/2023 | 10 | 1 | 9 |
| 22222 | 01/03/2021 | 11 | 5 | 6 | 01/03/2022 | 16 | 10 | 6 | 01/03/2023 | 12 | 5 | 7 |
| 33333 | 01/05/2021 | 12 | 5 | 7 | 01/05/2022 | 17 | 10 | 7 | 01/05/2023 | 14 | 9 | 5 |
As you can see, every report have 3 columns after it.
I wanted to unpivot it and make the table more comfortable to work it, but the customer said that every year he will add 4 new columns. I built for him a new template to work with but he doesnt want to use it.
How can I unpivot this table automatically without do it again next year (because he will add new columns)?
1 Reply
- amitchandak
Super User
uda123 , CHeck if this power query Script code can help
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VY7bCQAhDAR7ybdwSXxxtYj9t3FuiDkNKujsDo5BgqFELM9ayoqL8Dqq7yAKUk8cKO9XtF+aaZBiPJNDLN5sJzFx24oLmVi91E2cMZ6pIf4zBzFx3+ILmbjYb1Gc8wM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Company ID" = _t, Report1 = _t, Income = _t, Expensses = _t, profit = _t, Report2 = _t, Income.1 = _t, Expensses.1 = _t, profit.1 = _t, Report3 = _t, Income.2 = _t, Expensses.2 = _t, profit.2 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Company ID", Int64.Type}, {"Report1", type date}, {"Income", Int64.Type}, {"Expensses", Int64.Type}, {"profit", Int64.Type}, {"Report2", type date}, {"Income.1", Int64.Type}, {"Expensses.1", Int64.Type}, {"profit.1", Int64.Type}, {"Report3", type date}, {"Income.2", Int64.Type}, {"Expensses.2", Int64.Type}, {"profit.2", Int64.Type}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Company ID"}, "Attribute", "Value"), #"Split Column by Character Transition" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Attribute.1", "Attribute.2"}), #"Replaced Value" = Table.ReplaceValue(#"Split Column by Character Transition",".","",Replacer.ReplaceText,{"Attribute.1"}), #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",null,"1",Replacer.ReplaceValue,{"Attribute.2"}), #"Pivoted Column" = Table.Pivot(#"Replaced Value1", List.Distinct(#"Replaced Value1"[Attribute.1]), "Attribute.1", "Value", List.Max) in #"Pivoted Column"
