Reshaping data from columns to additional rows

[jaharris91]

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?

[TomMartens]

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]

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]

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]

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!

[Irwan · Accepted solution]

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.