transpose Data

[Anonymous]

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

[Anonymous]

Thank you guy's i've solved the problem on my sql query direct.

[Ashish_Mathur · Accepted solution]

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.

[lbendlin]

Don't transpose anything. Let the matrix visual do the work for you.