Forum Discussion
transpose Data
Hello,
Please a a junior in Bi and i've this challenge to do can u please assist me ?
I've this table
code pay No amount date
g124 2 100000 2/4/2024
g124 1 500 2/5/2018
g124 3 40000 2/4/2019
g126 5 800 2/5/2020
g126 4 700 2/5/2017
g127 1 782000 6/5/2022
g127 2 500000 10/5/2021
g127 3 69222 2/5/2020
g128 1 150000 12/5/2023
g129 1 800000 2/5/2024
i need to transpose this table such a way that i'll get each amount listed for a particluar code like
code 1 date 2 date 3 date
g124 100000 2/4/2024 500 2/5/2018 40000 2/4/2019
---
----
----
Thanks
Hi,
Just in case you want a Power Query solution, try this code
let Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"code", type text}, {"pay", Int64.Type}, {"No amount", Int64.Type}, {"date", type date}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"code", "pay"}, "Attribute", "Value"), #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Unpivoted Other Columns", {{"pay", type text}}, "en-IN"),{"Attribute", "pay"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"), #"Pivoted Column" = Table.Pivot(#"Merged Columns", List.Distinct(#"Merged Columns"[Merged]), "Merged", "Value") in #"Pivoted Column"Hope this helps.
3 Replies
Hi,
Just in case you want a Power Query solution, try this code
let Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"code", type text}, {"pay", Int64.Type}, {"No amount", Int64.Type}, {"date", type date}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"code", "pay"}, "Attribute", "Value"), #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Unpivoted Other Columns", {{"pay", type text}}, "en-IN"),{"Attribute", "pay"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"), #"Pivoted Column" = Table.Pivot(#"Merged Columns", List.Distinct(#"Merged Columns"[Merged]), "Merged", "Value") in #"Pivoted Column"Hope this helps.
Don't transpose anything. Let the matrix visual do the work for you.
