Forum Discussion

jaharris91's avatar
jaharris91
New Member
1 year ago
Solved

Reshaping data from columns to additional rows

I have an excel file with invoicing information that I'm trying to reshape into a more usable format for Power BI. The data gives in each row, the month something was billed, invoicing and vendor inf...
Need Help
Tips and Tricks
Tutorial Requests
  • Irwan's avatar
    Irwan
    1 year ago

    hello jaharris91 

     

    i think unpivot column in PQ should do the trick. then filter row for value not equal 0. After that you can rename the header to your preferences.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hVExDsIwDPxLBVsVxY6d0JGCGFhYmKg6AGJl4v/CjpIWQksX+2Rbd7lL11Wn+6uqK0BH0ryW7V7Kiq011gpiw2sdYDBs42YCpdvp3teljJdy3kVWLmTAmcBx4zMC5ERH1i0JHa9PZfNhI40oqKGoBKUhTm8HyzMuZjY/Ms0YW+BvETLg4rxJAAbybArzxOXjQeXwuCm9xCYNUVXayFqmhpSyopDR4FcQfhhKyId0Ny0Wg2svinhB6O/fj5d9/wY=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Billing Month" = _t, #"Invoice #" = _t, IO = _t, Vendor = _t, #"Vendor Invoice Amount" = _t, #"Admin%" = _t, Admin = _t, #"Billed Total (includes Admin)" = _t, Team1 = _t, Team2 = _t, Team3 = _t, Team4 = _t, Team5 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Billing Month", type text}, {"Invoice #", Int64.Type}, {"IO", Int64.Type}, {"Vendor", type text}, {"Vendor Invoice Amount", type text}, {"Admin%", Percentage.Type}, {"Admin", type text}, {"Billed Total (includes Admin)", type text}, {"Team1", type text}, {"Team2", type text}, {"Team3", type text}, {"Team4", type text}, {"Team5", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Billing Month", "Invoice #", "IO", "Vendor", "Vendor Invoice Amount", "Admin%", "Admin", "Billed Total (includes Admin)"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Columns", each [Value] <> "$0.00")
    in
    #"Filtered Rows"

     

    Hope this will help.

    Thank you.

TomMartens's avatar
TomMartens
Super User
1 year ago

Hey jaharris91 ,

this is in addition to what Irwan already mentioned.

You can leverage the transform "UnPivot", but instead of marking the Teams column, I recommend marking the non-teams columns as in the following screenshot:

When selecting the command "Unpivot Other Columns," the remaining columns will be transformed into new rows, where each column becomes a new row. This then will also recognize new Teams. This is then shown in the next screenshot:

 

Hopefully, this adds some insights.

Regards,

Tom

v-aatheeque's avatar
v-aatheeque
Community Support
1 year ago

Hi jaharris91 ,

If our response addressed by the community member for your query, please mark it as Accept Answer and click Yes if you found it helpful.

Should you have any further questions, feel free to reach out.
Thank you for being a part of the Microsoft Fabric Community Forum!

  • v-aatheeque's avatar
    v-aatheeque
    Community Support
    1 year ago

    Hi jaharris91 ,

    We haven’t heard back from you regarding our previous response and wanted to check if your issue has been resolved.

    If it has, please consider clicking “Accept Answer” and “Yes” if you found the response helpful.
    If you still have any questions or need further assistance, feel free to let us know — we're happy to help!

    Thank you!

    • v-aatheeque's avatar
      v-aatheeque
      Community Support
      1 year ago

      Hi jaharris91 

      If our response addressed by the community members is resolved for your query, please mark it as Accept Answer and click Yes if you found it helpful.

      Should you have any further questions, feel free to reach out.
      Thank you for being a part of the Microsoft Fabric Community Forum!