Forum Discussion
Solved
Making new columns using row values as names & filling those new columns with values and conditions
I wonder if there's a way to turn table 1 into table2 ?
Basically, I am trying to use concat "TEST_" and the year column from table 1 to form new columns in table2. The values in table 2 is also populated using values from Table1 with conditions. For example [if Year=202201, then Table2[TEST_202201]= Table1[Amount] Grouped by Table1[Name].
I tried to use pivot tables and list.transform({1..3},each _), but its not working properly. Thank you in advance.
serlart So like this?
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMjIwVNJRMjQAEgGpecmZOUqxOlAJI6CYMUjCKT8/GyEMUm9siEW9MVDMxBikKzyxJLVIKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Year = _t, Amount = _t, Name = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Amount", Int64.Type}, {"Name", type text}}), #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Changed Type", {{"Year", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Changed Type", {{"Year", type text}}, "en-US")[Year]), "Year", "Amount", List.Sum) in #"Pivoted Column"
1 Reply
- Greg_Deckler
Community Champion
serlart So like this?
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMjIwVNJRMjQAEgGpecmZOUqxOlAJI6CYMUjCKT8/GyEMUm9siEW9MVDMxBikKzyxJLVIKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Year = _t, Amount = _t, Name = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Amount", Int64.Type}, {"Name", type text}}), #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Changed Type", {{"Year", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Changed Type", {{"Year", type text}}, "en-US")[Year]), "Year", "Amount", List.Sum) in #"Pivoted Column"