Help with Deriving new Table based on specific set of Rules

[nssidhu]

 

I need to derive new table(bottom one) based on specific set of rules.
The rule is to have Originations, PlaceOfDelivery, UltimateDestination columns by each category(General, summmarizeDock Sale & Gov Sales) as depicted in the bottom table in above picture.
In the top table i have only one origination column but i get three origination columns in the bottom table because i have 3 categories(General, summmarizeDock Sale & Gov Sales). So i want to summarize the each category sale by state. I have also attached sample Data file.

In short, Sum of amounts by Originations, PlaceOfDelivery, UltimateDestination for Each Category for Each State as depicted in the bottom table.

[dkay84_PowerBI]

Is there a reason why you want to use DAX only?

[nssidhu]

Preferably, since i already know it, but any solution is acceptable.

[dkay84_PowerBI]

The Query Editor has pivot/unpivot functionality right in the UI.  I suggest you start there, or upload a sample table and we can help you with the required approach.

[nssidhu]

This is not a straight Transform. I have updated the title so as to not confuse with transpose/pivot

[dkay84_PowerBI · Accepted solution]

I wasn't sure how you wanted to handle the tax year, type and entity values, but based on your example outcome, here is how I solved it (using the Query Editor):

 1. Create a table with a column of all unique Locations (from all location columns; Origination, Delivery and Ultimate)

 2. Create a reference table to your original table using the following M code:

let
    Source = Original Table,
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {"Amount"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Columns", "Category", each if [Attribute] = "Category" then [Value] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Category"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Attribute] <> "Category")),
    #"Merged Columns" = Table.CombineColumns(#"Filtered Rows",{"Category", "Attribute"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
    #"Renamed Columns" = Table.RenameColumns(#"Merged Columns",{{"Value", "Location"}})
in
    #"Renamed Columns"

  3. Close and apply, and create a relationship between the unique Location table and the Location column in this new table.

  4. Create a matrix visual with the unique Location as Rows, the "Merged" colum as Columns (you can rename if you want) and the "Amount" column as Values (rename if needed). 

Done: