Forum Discussion
uda123
3 years agoFrequent Visitor
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...
amitchandak
3 years agoSuper 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"