cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nssidhu Frequent Visitor
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

Accepted Solutions
Highlighted
dkay84_PowerBI New Contributor
New Contributor

Re: Help with Deriving new Table based on Rules

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
dkay84_PowerBI New Contributor
New Contributor

Re: Help with Transposing / Pivoting Table

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

nssidhu Frequent Visitor
Frequent Visitor

Re: Help with Transposing / Pivoting Table

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

dkay84_PowerBI New Contributor
New Contributor

Re: Help with Transposing / Pivoting Table

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 Frequent Visitor
Frequent Visitor

Re: Help with Deriving new Table based on Rules

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

Highlighted
dkay84_PowerBI New Contributor
New Contributor

Re: Help with Deriving new Table based on Rules

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

Helpful resources

Announcements
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 6 members 2,743 guests
Please welcome our newest community members: