Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
In the SQL database I am using I have a bunch of factories, each of these factories will have a number of data entries representing their outputs/inputs. These are represented by three columns: Activity, Product, and Volume. I want to have the PowerBI Matrix expand out the Activity and Product table to instead have one column per combination with the volumes for all entries from that factory in a given month being summed in it's respective column.
e.g. if the columns contain the following Activity: (A, B, C, D) and Product (1, 2, 3) my specific then I would want to have 12 columns in the matrix A1, A2, A3, B1, ..., D2, D3. If my factory had 3 different volumes in the month reported for A3 then they would all get summed into the A3 column.
I haven't been able to find a way to expand out these columns as such, and am hoping I may be able to get some advice.
Here is some example data
expected Result (approximately) Something like this, but obviously in a PowerBI Matrix
I cannot give official data that I will be using for the project due to some confidential information, but this is a quite accurate to the overall goal. The real project has more than just 2 activities and 3 products
Solved! Go to Solution.
In PowerQuery:
1. Highlight Activity and Product columns, and select “Merge Columns” using "_" as separator.
2. Highlight “Merged” column and select “Pivot Column” using Volume as “Values Column”.
let
Source = Excel.Workbook(
File.Contents("C:\Power_BI\PBI Community\expand out columns Matrix\RawData.xlsx"),
null,
true
),
Sheet1_Sheet = Source{[Item = "Sheet1", Kind = "Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars = true]),
#"Changed Type" = Table.TransformColumnTypes(
#"Promoted Headers",
{
{"Date", type date},
{"Facility ID", type text},
{"Activity", type text},
{"Product", type text},
{"Volume", Int64.Type}
}
),
#"Merged Columns" = Table.CombineColumns(
#"Changed Type",
{"Activity", "Product"},
Combiner.CombineTextByDelimiter("_", QuoteStyle.None),
"Merged"
),
#"Pivoted Column" = Table.Pivot(
#"Merged Columns",
List.Distinct(#"Merged Columns"[Merged]),
"Merged",
"Volume",
List.Sum
)
in
#"Pivoted Column"
@Anonymous You can create a DAX calculated column like: Column = [Activity] & "_" & [Product]
In PowerQuery:
1. Highlight Activity and Product columns, and select “Merge Columns” using "_" as separator.
2. Highlight “Merged” column and select “Pivot Column” using Volume as “Values Column”.
let
Source = Excel.Workbook(
File.Contents("C:\Power_BI\PBI Community\expand out columns Matrix\RawData.xlsx"),
null,
true
),
Sheet1_Sheet = Source{[Item = "Sheet1", Kind = "Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars = true]),
#"Changed Type" = Table.TransformColumnTypes(
#"Promoted Headers",
{
{"Date", type date},
{"Facility ID", type text},
{"Activity", type text},
{"Product", type text},
{"Volume", Int64.Type}
}
),
#"Merged Columns" = Table.CombineColumns(
#"Changed Type",
{"Activity", "Product"},
Combiner.CombineTextByDelimiter("_", QuoteStyle.None),
"Merged"
),
#"Pivoted Column" = Table.Pivot(
#"Merged Columns",
List.Distinct(#"Merged Columns"[Merged]),
"Merged",
"Volume",
List.Sum
)
in
#"Pivoted Column"
Can you please post the sample data as actual data? (As opposed to an image)
Proud to be a Super User!
Paul on Linkedin.
User | Count |
---|---|
102 | |
91 | |
87 | |
79 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
75 | |
64 |