Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

How do I transpose my data but then also create row headers?

I want to get the "prod dt" values as my column headings and then 1st multi borrower, owner name, account number, and net balance balance plus neg reserve as the row headers. The first photo is how my data is now and the 2nd photo is how I want my table to look like.

 

 

 

 

data protection
Need Help
Tips and Tricks

2 Replies

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi Anonymous ,

    According to your description, in Power BI, it can‘t be exactly the same as in Excel, please see if this suggestion helps.

     Copy-paste the code in a blank query in PowerQuery.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZYxBCoAwDAT/krPQbtJqPao39Qel//+GKShGQxgIDDu1EkfmoFdooEXZFVbAQm34+lU5ulNSHp3flPPeT2V+vQSB6fcfEc7bPsDO2z7E7FOQ+OsnZOc//dHvn373Ewq1dgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Prod Dt" = _t, #"1st Multi Borrower" = _t, #"Owner Name" = _t, AccountNumber = _t, NetBalancePlusNegReserve = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Prod Dt", type date}, {"1st Multi Borrower", type text}, {"Owner Name", type text}, {"AccountNumber", Int64.Type}, {"NetBalancePlusNegReserve", Int64.Type}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Prod Dt", "Prod Dt - Copy"),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Duplicated Column", {{"Prod Dt", type text}}, "en-GB"), List.Distinct(Table.TransformColumnTypes(#"Duplicated Column", {{"Prod Dt", type text}}, "en-GB")[#"Prod Dt"]), "Prod Dt", "NetBalancePlusNegReserve", List.Sum),
    #"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",{{"28/02/2022", "28/02/2022-NetBalancePlusNegReserve"}, {"31/03/2022", "31/03/2022-NetBalancePlusNegReserve"}, {"30/04/2022", "30/04/2022-NetBalancePlusNegReserve"}}),
    #"Pivoted Column1" = Table.Pivot(Table.TransformColumnTypes(#"Renamed Columns", {{"Prod Dt - Copy", type text}}, "en-GB"), List.Distinct(Table.TransformColumnTypes(#"Renamed Columns", {{"Prod Dt - Copy", type text}}, "en-GB")[#"Prod Dt - Copy"]), "Prod Dt - Copy", "AccountNumber", List.Sum),
    #"Renamed Columns1" = Table.RenameColumns(#"Pivoted Column1",{{"31/03/2022", "31/03/2022-AccountNumber"}, {"30/04/2022", "30/04/2022-AccountNumber"}, {"28/02/2022", "28/02/2022-AccountNumber"}})
in
    #"Renamed Columns1"

    Best Regards,
    Community Support Team _ kalyj

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi Anonymous ,

    Is your problem solved?? If so, Would you mind accept the helpful replies as solutions? Then we are able to close the thread. More people who have the same requirement will find the solution quickly and benefit here. Thank you.

    Best Regards,
    Community Support Team _ kalyj