Forum Discussion
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 information, total billed, but then a column breaking out each team that the billing was for. See below:
| Billing Month | Invoice # | IO | Vendor | Vendor Invoice Amount | Admin% | Admin | Billed Total (includes Admin) | Team1 | Team2 | Team3 | Team4 | Team5 |
| Oct | 1234 | 64 | AD | $500.00 | 5.5% | $27.50 | $527.50 | $527.50 | $0.00 | $0.00 | $0.00 | $0.00 |
| Oct | 1234 | 66 | TC | $250.00 | 5.5% | $13.75 | $263.75 | $125.00 | $403.00 | $0.00 | $0.00 | $0.00 |
| Jan | 5678 | 447 | AC | $100.00 | 5.5% | $5.50 | $105.50 | $0.00 | $0.00 | $105.50 | $0.00 | $0.00 |
| Jan | 5678 | 94 | AD | $75.00 | 5.5% | $4.13 | $79.13 | $10.00 | $15.00 | $20.00 | $34.13 | $0.00 |
| Feb | 9123 | 22 | AB | $450.00 | 5.5% | $24.75 | $474.75 | $100.00 | $102.00 | $105.00 | $167.75 | $0.00 |
| Feb | 9123 | 47 | BZ | 450 | 5.5% | $24.75 | $474.75 | $0.00 | $0.00 | $0.00 | $0.00 | $474.75 |
What I need is a row for each team split of the invoice. So essentially, I need the above chart to be transformed to this:
| Billing Month | Invoice # | IO | Vendor | Vendor Invoice Amount | Admin% | Admin | Billed Total (includes Admin) | Team | Team Total |
| Oct | 1234 | 64 | AD | $500.00 | 5.5% | $27.50 | $527.50 | Team1 | $527.50 |
| Oct | 1234 | 66 | TC | $250.00 | 5.5% | $13.75 | $263.75 | Team1 | $125.00 |
| Oct | 1234 | 66 | TC | $250.00 | 5.5% | $13.75 | $263.75 | Team2 | $403.00 |
| Jan | 5678 | 447 | AC | $100.00 | 5.5% | $5.50 | $105.50 | Team3 | $105.50 |
| Jan | 5678 | 94 | AD | $75.00 | 5.5% | $4.13 | $79.13 | Team1 | $10.00 |
| Jan | 5678 | 94 | AD | $75.00 | 5.5% | $4.13 | $79.13 | Team2 | $15.00 |
| Jan | 5678 | 94 | AD | $75.00 | 5.5% | $4.13 | $79.13 | Team3 | $20.00 |
| Jan | 5678 | 94 | AD | $75.00 | 5.5% | $4.13 | $79.13 | Team4 | $34.13 |
| Feb | 9123 | 22 | AB | $450.00 | 5.5% | $24.75 | $474.75 | Team1 | $100.00 |
| Feb | 9123 | 22 | AB | $450.00 | 5.5% | $24.75 | $474.75 | Team2 | $102.00 |
| Feb | 9123 | 22 | AB | $450.00 | 5.5% | $24.75 | $474.75 | Team3 | $105.00 |
| Feb | 9123 | 22 | AB | $450.00 | 5.5% | $24.75 | $474.75 | Team4 | $167.75 |
| Feb | 9123 | 47 | BZ | 450 | 5.5% | $24.75 | $474.75 | Team5 | $474.75 |
Is this possible to do in power query? If not, is anyone aware of how I can do this in excel before loading into power bi?
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.
5 Replies
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
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!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!
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.
