create new row for each pair of columns from excel workbook

[ddurose]

Hi, I am new to Power Query - I am looking to take in an excel sheet as the source whose input format looks like this. It shows contributions into multiple accounts and each contribution is split across multiple contributors. Obviously, contributor 1 is tied to amount 1, contributor 2 is tied to amount 2, etc.

Account #	Date Received	Contributor 1	Amount 1	Contributor 2	Amount 2	Contributor 3	Amount 3
123	10/1/22	ABC Company	$100	DEF Company	$250	GHI Company	$500
456	10/5/22	JKL Company	$300

I'd like to transform the data so that each Contributor/Amount pairing becomes its own row and the other data related to the contribution (account # and date received) is repeated. Every contribution will have at least one contributor but it can vary as to how many. This is the output I'm looking for:

Account #	Date Received	Contribution	Amount
123	10/1/22	ABC Company	$100
123	10/1/22	DEF Company	$250
123	10/1/22	GHI Company	$500
456	10/5/22	JKL Company	$300

Any insights would be greatly appreciated!

[Dinesh_Suranga · Accepted solution]

@ddurose 

Hi Use following M code.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRMjTQN9Q3MgKyHJ2cFZzzcwsS8yqBPBVDAwMg5eLqhixoZAoSdPfwRBY0BaqM1YlWMjE1gxhoCjHQy9sHWZkx2EAEio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Account #" = _t, #"Date Received" = _t, #"Contributor 1" = _t, #"Amount 1" = _t, #"Contributor 2" = _t, #"Amount 2" = _t, #"Contributor 3" = _t, #"Amount 3" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Account #", Int64.Type}, {"Date Received", type date}, {"Contributor 1", type text}, {"Amount 1", Currency.Type}, {"Contributor 2", type text}, {"Amount 2", Currency.Type}, {"Contributor 3", type text}, {"Amount 3", Currency.Type}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Account #", "Date Received"}, "Attribute", "Value"),
#"Added Index" = Table.AddIndexColumn(#"Unpivoted Columns", "Index", 1, 1, Int64.Type),
#"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 0, 1, Int64.Type),
#"Merged Queries" = Table.NestedJoin(#"Added Index1", {"Index"}, #"Added Index1", {"Index.1"}, "Added Index1", JoinKind.LeftOuter),
#"Expanded Added Index1" = Table.ExpandTableColumn(#"Merged Queries", "Added Index1", {"Value"}, {"Added Index1.Value"}),
#"Added Custom" = Table.AddColumn(#"Expanded Added Index1", "Custom", each Number.IsOdd([Index])),
#"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = true)),
#"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Value", "Contribution"}, {"Added Index1.Value", "Amount"}}),
#"Filtered Rows1" = Table.SelectRows(#"Renamed Columns", each ([Contribution] <> "")),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows1",{"Account #", "Date Received", "Contribution", "Amount"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Amount", Currency.Type}, {"Contribution", type text}})
in
#"Changed Type1"

Thank you.

[ddurose]

@Dinesh_Suranga thank you for this. I was able to implement it successfully. I have a follow-up question. I am looking to use this table in a visual. The report user will come in with a user role tied to their company (companies are contained in a separate table). So the company table has a relationship with the contributor column in the table you created for me which means it will be filtered down to only contributions made by "Company ABC" for instance. I have a requirement to show the amount given by "Company ABC" which is already done but also show the total amount given by other companies on the same contribution. I'm not sure how to do this since the table is getting filtered down to just contributions by the company of the user logged in. I need to somehow sum up the amount given by other companies and show a total contribution. Here is the output I am looking for in the table visual (using the sample data I put in my original request). Let's say I am logged in as "Company ABC":

Account #	Date Received	Amount from your Company	Amount from Others	Total Contribution
123	10/1/22	$100	$750	$850

If I was logged into the report as "Company DEF", it would look like this:

Account #	Date Received	Amount from your Company	Amount from Others	Total Contribution
123	10/1/22	$250	$600	$850

Thanks in advance!

[Dinesh_Suranga]

@ddurose 
Hi,

Try following DAX codes.

Amount from your Company = Sum('Table'[Amount])

Amount from Others = CALCULATE(SUM('Table'[Amount]), All('Table'[Contribution]) , ALL('Table'[Date])) - [Amount from your Company]

Total Contribution = CALCULATE(SUM('Table'[Amount]), All('Table'[Contribution]) , ALL('Table'[Date]))

If I answer your question, Please mark my reply as a solution.

Thank you.

[ddurose]

It doesn't seem like the "All" is working - is it because I am coming in with a security role that filters the available data down to a single contributor like "DEF Company" and so even though we say "All", it ignores that? 

[wdx223_Daniel]

=#table({"Account#","Date Received","Contribution","Amount"},List.TransformMany(Table.ToRows(PreviousStepName),each List.Select(List.Split(List.Skip(_),3),each _{0}<>null),(x,y)=>{x{0}}&y))

[alannavarro]

I tried to use your solution and I keep getting an error... but it was a great solution! 

To make it work I just first merged the columns that I wanted to keep , used your code and then just split the columns again.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRMjTQN9Q3MgKyHJ2cFZzzcwsS8yqBPBVDAwMg5eLqhixoZAoSdPfwRBY0BaqM1YlWMjE1gxhoCjHQy9sHWZkx2EAEio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Account #" = _t, #"Date Received" = _t, #"Contributor 1" = _t, #"Amount 1" = _t, #"Contributor 2" = _t, #"Amount 2" = _t, #"Contributor 3" = _t, #"Amount 3" = _t]),
#"Merged Columns" = Table.CombineColumns(Source,{"Account #", "Date Received"},Combiner.CombineTextByDelimiter("!", QuoteStyle.None),"Merged"),
result = #table({"Account#","Contribution","Amount"},
List.TransformMany(Table.ToRows(#"Merged Columns"),
each List.Select(List.Split(List.Skip(_),2),each _ <> null),
(x,y)=> {x{0}}&y)),
#"Filtered Rows" = Table.SelectRows(result, each ([Contribution] <> "")),
#"Split Column by Delimiter" = Table.SplitColumn(#"Filtered Rows", "Account#", Splitter.SplitTextByDelimiter("!", QuoteStyle.Csv), {"Account#", "Date received"})
in
#"Split Column by Delimiter"

[wdx223_Daniel]

no need to merge and to split

= #table({"Account#","Date Received","Contribution","Amount"},
List.TransformMany(Table.ToRows(Source),
each List.Select(List.Split(List.Skip(_,2),2),each _{0} <> null and _{0}<>""),
(x,y)=> {x{0},x{1}}&y))