Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
nssidhu
Frequent Visitor

Help with Deriving new Table based on specific set of Rules

SampleData.jpg 

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.

1 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:

Capture.PNG

View solution in original post

5 REPLIES 5

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

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

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.

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

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:

Capture.PNG

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.