Aggregate Multiple Columns to create new rows which share unique identifiers

[ElliotP]

Afternoon,

I've hit a snag wherein I would like to be able to use a conditional column upon my visuals, but upon un-nesting my data, I've found I have a lot more columns and no idea how to aggregate them all together, so;

I have a table of data from an Azure SQL table which was json, but I used data factory to un-nest the heavily nested json. Now, I have a large number of columns per unique entry wherin for a transaction where there is two items it reads:

Transaction ID1, Date, ItemName1, ItemName1Sales, ItemName2, ItemName2Sales.

I would like to be able to transpose/pivot as so it reads;

TransactionID|Date|ItemName|ItemNameSales

TransactionID1, Date, ItemName1, ItemName1Sales

TransactionID1, Date, ItemName2, ItemName2Sales

This article I think exactly what I would like, http://community.powerbi.com/t5/Desktop/split-multiple-column-values-into-multiple-rows/td-p/144047, especially @MarcelBeug's solution.

What I'm not sure about is how to do it for my specific set of data in regards to using aggregative columns, etc. If anyone would mind walking me through the steps that would be greatly appreciated.

My current understanding is for my case;

1. I unpivot other columns except TransactionID

2. ? onwards.

[MarcelBeug]

You need to unpivot other columns except TransactionID and Date.

Otherwise proceed as explained in the other post, except that the formula, to get rid of the digits from the unpivoted column headers, needs some adjustment as in the code below (step #"Trimmed Text" removes all digits)..

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"TransactionID", Int64.Type}, {"Date", type date}, {"ItemName1", type text}, {"ItemName1Sales", Int64.Type}, {"ItemName2", type text}, {"ItemName2Sales", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"TransactionID", "Date"}, "Attribute", "Value"),
    #"Trimmed Text" = Table.TransformColumns(#"Unpivoted Other Columns",{{"Attribute", each Text.Combine(Text.SplitAny(_,"0123456789"))}}),
    #"Added Index" = Table.AddIndexColumn(#"Trimmed Text", "Index", 0, 1),
    #"Integer-Divided Column" = Table.TransformColumns(#"Added Index", {{"Index", each Number.IntegerDivide(_, 2), Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Integer-Divided Column", List.Distinct(#"Integer-Divided Column"[Attribute]), "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index"})
in
    #"Removed Columns"

[ElliotP]

Evening,

Amazing, thank you so much.

After working through the powerquery I get to this error:

Expression.Error: There were too many elements in the enumeration to complete the operation.

Its coming up as an error in my modifier columns. From my googling and my understanding this is an error as a result of there being too many or duplicate results in this type of column.

Thoughts? https://gyazo.com/0c4addf0a0f6b8d76f577ef0eadc1a5b

I can send you my pbix if you wouldn't mind, but I'm not sure why this is erroring. Maybe something to do with the column types?

[ElliotP]

Would it be best to do the same thing as I did with the different naming groups and give the error columns different names such as "modifiers_applied_money_amount1" and "modifiers_applied_money_amount2" and then simply renaming all later in the powery query?

[MarcelBeug]

The error indicates that you have duplicates rows in your data, just before pivoting.

So either something went wrong with adding the column with integer-divided numbers {0,0,1,1,2,2,etcetera) or your columns are different from your example, i.e. starting with TransactionID, Date and then pairs of ItemNames and ItemNameSales.

A screen shot from your interim results after step "Integer-Divided Column" would give more information.

It should look like:

[ElliotP]

hmm, duplicate rows

https://gyazo.com/7b8b3cfbd42ba8dd22024c5a35f3b026

[MarcelBeug · Accepted solution]

That is clearly a completely different situation.

It doesn't fit the referenced topic at all.

My advice would be to start a new topic from scratch, clearly and exactly describing your specific case.

I suspect you want rows with the amount type, the amount and the amount currency.

Next to that, you have many more columns that shouldn't be unpivoted.