How to combine multiple pairs of columns into a single pair

[fishboy96]

Hi all, i'm wondering if anyone could please help me get back on track with my latest challenge.  I've got a transactional table that contains five different cost amounts, and for each there is an account code associated with the amount.  I'm trying to transform these 10 columns (5-pairs) so I have a single "Transaction Cost" column with the corresponding "Transaction Account" column.  I understand it will grow each row into 5 rows in my table but I'm okay with that. 

I've tried everything I can think of and still cannot get accurate results.  Admittedly, I am a self-taught PowerBI addict with no formal training on DAX and I've tried reading and searching for hours and have come up without a good answer. 

Any help is greatly appreciated!!!

Thank you,
George M.

[v-jiascu-msft · Accepted solution]

Hi George,

I created two solutions. Please download it the demo from the attachment. 

1. Transform data using UI functions. 

2. Using Power Query M functions. You can modify the code and paste it in the Advanced Editor.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Rcy7DcAwCEXRXahdmI/jpIwyhsX+awSQDc3TldBhLfgQGrw+2L3IRqLYZkZJXCOHJ3fQZpQOpaQj6V0Ui8qmfCgnvZI+RamovVb9AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Customer = _t, #"Account 1" = _t, #"Cost 1" = _t, #"Account 2" = _t, #"Cost 2" = _t, #"Account 3" = _t, #"Cost 3" = _t, #"Account 4" = _t, #"Cost 4" = _t, #"Account 5" = _t, #"Cost 5" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer", type text}, {"Account 1", type text}, {"Cost 1", Int64.Type}, {"Account 2", type text}, {"Cost 2", Int64.Type}, {"Account 3", type text}, {"Cost 3", Int64.Type}, {"Account 4", type text}, {"Cost 4", Int64.Type}, {"Account 5", type text}, {"Cost 5", Int64.Type}}),
    result = Table.Combine({Table.RenameColumns(Table.SelectColumns(#"Changed Type", {"Customer", "Account 1", "Cost 1"}), {{"Account 1", "Account"},{"Cost 1", "Cost"}}), 
    Table.RenameColumns(Table.SelectColumns(#"Changed Type", {"Customer", "Account 2", "Cost 2"}), {{"Account 2", "Account"},{"Cost 2", "Cost"}}),
    Table.RenameColumns(Table.SelectColumns(#"Changed Type", {"Customer", "Account 3", "Cost 3"}), {{"Account 3", "Account"},{"Cost 3", "Cost"}}),
    Table.RenameColumns(Table.SelectColumns(#"Changed Type", {"Customer", "Account 4", "Cost 4"}), {{"Account 4", "Account"},{"Cost 4", "Cost"}}),
    Table.RenameColumns(Table.SelectColumns(#"Changed Type", {"Customer", "Account 5", "Cost 5"}), {{"Account 5", "Account"},{"Cost 5", "Cost"}})})
in
    result

Best Regards,
Dale

[fishboy96]

Thank You SO MUCH!!!  Your solution is brilliant :).  The problem I had was I had different labels for each Cost and Account field, but by changing the cost names to Cost 1, Cost 2 etc. and Account 1, Account 2 etc., it works perfect!  My data looks exactly like I need it to now and I'm so grateful for your assistance. 

George

[tarunsingla]

Not sure how your dataset looks like. Would really help if you can post samples of your data input and the desired output.

One thing that you can try is (if the data size is not huge and the columns are limited), in power bi query editor, create 5 different datasets from the same input data source, and pull 2 columns (pair) in each dataset (so that you have 5 datasets with 5 different pairs) and then create a final dataset by appending data from those 5 different tables.

[fishboy96]

Thank you Tarunsingla for your response.  I considered doing what you suggested but was hoping to find a less cumbersome solution.  I appreciate you taking the time to offer your assistance!

George