Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
Hi all
Is it possible to do this calculated column with Power Query?
Mix by product =
DIVIDE(
CALCULATE(
SUMX(
'SKU by line - Official',
'SKU by line - Official'[Production by bottler]
)
)
,
CALCULATE(
SUMX(
'SKU by line - Official','SKU by line - Official'[Production by bottler]),
ALLEXCEPT(
'SKU by line - Official',
'SKU by line - Official'[Manufacturing plant of production],
'SKU by line - Official'[Manufacturing line],
'SKU by line - Official'[Date]
)
)
)
The table is called SKU by line.
In excel it looks like:
Here is the pbix: https://1drv.ms/u/s!ApgeWwGTKtFdh13Pj8JD18lE9HC8?e=eSBaJi
Thanks community.
Solved! Go to Solution.
Hello - yes, this can be done in Power Query. Here is an example...
Goal: Add a column that contains the value of the current ListPrice column divided by the sum of the ListPrice column for all records with matching values in the Color, Size and Style columns.
BEFORE
AFTER
SCRIPT (comments are included that explain what's going on).
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pZbLjpswFIZfxco6QQRsDNtUmqkqe9N0GqrpLGjkSVBcqAJM1bcvtqEDsY+plEXkSMn3n/9cfHl+Xj2h1Xp1OJet6Ffef6Jtqr7VXdUWZYV25UWgfX28NGukfs4CEq5e1jcg02AGgcwCeSfbsl/3CowSJVFXp81eCvEmEKtPNfokro34s0bqLzgNQgrR9C46XaSzIMtsWtcqynw0h2NrOg7voreLtNu5blYc+WgGxzZ0fBeNF2m389zgxIfnnugD7x23/+C9A5fP/XPF72RxvAwTh7Gyz0X1vQvDiDZo/6u+tv1yVosZOeLGdU9J7MU5jGtjBHtxBuMmMeJ3n88FDlb2RBX/UP98V/hSns5j4hQ7SZM4hUgOk8ZzCpFsRvLb0wGTbJ7trvyxmTYqzZys9puEMMthVjtOtjDLZuyTVeFE7eyPhXzdPJTVSVzRo6zfxGA4ioO+gwAY+0AcYEdEkykGQA5HNCDxge6Ipj4JADI4ogGpD3RHNMVRM/TQSekqTkyd7TA5ZgDIYVBbpSEAMhfYidEpVbPzQRZNUx7RV9G0xmMSzy7ggdAWaWQRHCSMt9gi2IywNz7Ft5vw31NhuqMS99lhfJJFCQ5LGOPW8WNJsJmE9T7Cqarv5+LYN2X6MEqdhLlMIotgFvFNSFn/HquVqnHTjsZLZqz29GlCYreE8ZktSXCPhLnFwiUJ5pEYbsLtkkY+EXn5Cw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Style = _t, Color = _t, Size = _t, ProductKey = _t, EnglishProductName = _t, ListPrice = _t]),
ChangeType = Table.TransformColumnTypes(Source,{{"ProductKey", Int64.Type}, {"EnglishProductName", type text}, {"Color", type text}, {"ListPrice", type number}, {"Size", type text}}),
Grouped = Table.Group(
ChangeType,
{"Style", "Color", "Size"}, // group rows by these columns
{
{"GroupedRows", // name of the column with the grouped tables
each // current iteration of the table...
let // create some variables
varTable = Table.Buffer ( _ ), // read the current nested table into memory
varSum = List.Sum([ListPrice]) // denominator
in
Table.AddColumn ( // add a new column
varTable, // to the grouped table
"Calculation", // new column name
each Value.Divide ( // new column value
Record.Field ( _, "ListPrice" ), // current row of the nested table
varSum // sum of grouped value
),
type table
)
}
}
),
// Expand all columns in the nested tables that are not present in the Grouped table.
expand = Table.ExpandTableColumn (
Grouped,
"GroupedRows",
List.Difference (
Table.ColumnNames ( Table.Combine ( Grouped[GroupedRows] ) ),
Table.ColumnNames ( Grouped )
)
),
#"Sorted Rows" = Table.Sort(expand,{{"ProductKey", Order.Ascending}}),
#"Changed Type" = Table.TransformColumnTypes(#"Sorted Rows",{{"ProductKey", Int64.Type}, {"EnglishProductName", type text}, {"ListPrice", Currency.Type}, {"Calculation", Percentage.Type}})
in
#"Changed Type"
Hello - yes, this can be done in Power Query. Here is an example...
Goal: Add a column that contains the value of the current ListPrice column divided by the sum of the ListPrice column for all records with matching values in the Color, Size and Style columns.
BEFORE
AFTER
SCRIPT (comments are included that explain what's going on).
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pZbLjpswFIZfxco6QQRsDNtUmqkqe9N0GqrpLGjkSVBcqAJM1bcvtqEDsY+plEXkSMn3n/9cfHl+Xj2h1Xp1OJet6Ffef6Jtqr7VXdUWZYV25UWgfX28NGukfs4CEq5e1jcg02AGgcwCeSfbsl/3CowSJVFXp81eCvEmEKtPNfokro34s0bqLzgNQgrR9C46XaSzIMtsWtcqynw0h2NrOg7voreLtNu5blYc+WgGxzZ0fBeNF2m389zgxIfnnugD7x23/+C9A5fP/XPF72RxvAwTh7Gyz0X1vQvDiDZo/6u+tv1yVosZOeLGdU9J7MU5jGtjBHtxBuMmMeJ3n88FDlb2RBX/UP98V/hSns5j4hQ7SZM4hUgOk8ZzCpFsRvLb0wGTbJ7trvyxmTYqzZys9puEMMthVjtOtjDLZuyTVeFE7eyPhXzdPJTVSVzRo6zfxGA4ioO+gwAY+0AcYEdEkykGQA5HNCDxge6Ipj4JADI4ogGpD3RHNMVRM/TQSekqTkyd7TA5ZgDIYVBbpSEAMhfYidEpVbPzQRZNUx7RV9G0xmMSzy7ggdAWaWQRHCSMt9gi2IywNz7Ft5vw31NhuqMS99lhfJJFCQ5LGOPW8WNJsJmE9T7Cqarv5+LYN2X6MEqdhLlMIotgFvFNSFn/HquVqnHTjsZLZqz29GlCYreE8ZktSXCPhLnFwiUJ5pEYbsLtkkY+EXn5Cw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Style = _t, Color = _t, Size = _t, ProductKey = _t, EnglishProductName = _t, ListPrice = _t]),
ChangeType = Table.TransformColumnTypes(Source,{{"ProductKey", Int64.Type}, {"EnglishProductName", type text}, {"Color", type text}, {"ListPrice", type number}, {"Size", type text}}),
Grouped = Table.Group(
ChangeType,
{"Style", "Color", "Size"}, // group rows by these columns
{
{"GroupedRows", // name of the column with the grouped tables
each // current iteration of the table...
let // create some variables
varTable = Table.Buffer ( _ ), // read the current nested table into memory
varSum = List.Sum([ListPrice]) // denominator
in
Table.AddColumn ( // add a new column
varTable, // to the grouped table
"Calculation", // new column name
each Value.Divide ( // new column value
Record.Field ( _, "ListPrice" ), // current row of the nested table
varSum // sum of grouped value
),
type table
)
}
}
),
// Expand all columns in the nested tables that are not present in the Grouped table.
expand = Table.ExpandTableColumn (
Grouped,
"GroupedRows",
List.Difference (
Table.ColumnNames ( Table.Combine ( Grouped[GroupedRows] ) ),
Table.ColumnNames ( Grouped )
)
),
#"Sorted Rows" = Table.Sort(expand,{{"ProductKey", Order.Ascending}}),
#"Changed Type" = Table.TransformColumnTypes(#"Sorted Rows",{{"ProductKey", Int64.Type}, {"EnglishProductName", type text}, {"ListPrice", Currency.Type}, {"Calculation", Percentage.Type}})
in
#"Changed Type"
Hi
Can you share your pbix to see the steps?
THanks.
Sure - you can also just create a blank query in your pbix, copy the script in its entirety, paste it into your blank query, and click ok.
Recreate Excel SUMIFS in Power Query.pbix
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.