Scenario:
Suppose we have a sample data with below fields:
[Year], [Month], [Product], [Gross_Sales], [COGS], [Operating_Expenses], [Interset_Taxes]
Suppose we have a sample data with below fields:
[Year], [Month], [Product], [Gross_Sales], [COGS], [Operating_Expenses], [Interset_Taxes]
Now, based on the [Gross_Sales] column, we need to generate the expense % columns as per below:
[% COGS], [% Operating_Expenses], [% Interset_Taxes]
We can achieve this Scenario using the following M-Query:
let
/* Original data source: */
Source = Table.FromRows(
Json.Document(
Binary.Decompress(
Binary.FromText("hZGxDsIwDET/pXOHxImd5AcYECyIreoQRIdKiCJAfD/xDVVpVbpcpOtd+uw0TUWGTFVX+3wvesrXfihnir6oS1Dv4VRtPaZ33aXoubt1n/7VD1pl1hgRFbUoMk8rx/wsesiP9/DQj05NsohHgTON//KIZ01zxC80LbzBI6xXk1hU4qIy4wFNSqhoMfA6TcClzmBgETgbNDEQVplQZDh/aISA7TGvDXDWedhgVKMdG7BLs8ETsEpnHEbgxQhzHiCTp/HNygra9gs=", BinaryEncoding.Base64),
Compression.Deflate
)
),
let _t = ((type nullable text) meta [Serialized.Text = true]) in
type table [
Year = _t, Month = _t, Product = _t, Gross_Sales = _t,
COGS = _t, Operating_Expenses = _t, Interest_Taxes = _t
]
),
/* Change column data types: */
ChangeType = Table.TransformColumnTypes(Source, {
{"Year", Int64.Type}, {"Month", type text}, {"Product", type text},
{"Gross_Sales", Int64.Type}, {"COGS", Int64.Type},
{"Operating_Expenses", Int64.Type}, {"Interest_Taxes", Int64.Type}
}),
/* List of columns based on which % columns will be generated: */
Cols_List = {"COGS", "Operating_Expenses", "Interest_Taxes"},
/* Generate additional Percentage Columns */
Add_Ratios = List.Accumulate(
Cols_List,
ChangeType,
(i, c) => Table.AddColumn(
i,
"% " & c,
each if ([Gross_Sales] = null or [Gross_Sales] = 0) then null
else (Record.Field( _, c) / [Gross_Sales]),
type nullable Percentage.Type
)
)
in
Add_Ratios
--------------------------------------------------------------
Notes:
--------------------------------------------------------------
Notes:
In the above List.Accumulate(i,c) function, the variables will work as per below:
c: The current column name from Cols_List being processed in that iteration.
i: The intermediate table result that accumulates with each added column. It starts as ChangeType and grows with each new % Column.
c: The current column name from Cols_List being processed in that iteration.
i: The intermediate table result that accumulates with each added column. It starts as ChangeType and grows with each new % Column.
If Cols_List = {"COGS", "Operating_Expenses"}, then the loop will work as:
First iteration: c = "COGS"
Adds column % COGS to ChangeType, result stored in i
Second iteration: c = "Operating_Expenses"
Adds column % Operating_Expenses to the table from step 1
And so on, until all columns in Cols_List have their corresponding % columns added.
Output:
let
/* Original data source: */
Source = Table.FromRows(
Json.Document(
Binary.Decompress(
Binary.FromText("hZGxDsIwDET/pXOHxImd5AcYECyIreoQRIdKiCJAfD/xDVVpVbpcpOtd+uw0TUWGTFVX+3wvesrXfihnir6oS1Dv4VRtPaZ33aXoubt1n/7VD1pl1hgRFbUoMk8rx/wsesiP9/DQj05NsohHgTON//KIZ01zxC80LbzBI6xXk1hU4qIy4wFNSqhoMfA6TcClzmBgETgbNDEQVplQZDh/aISA7TGvDXDWedhgVKMdG7BLs8ETsEpnHEbgxQhzHiCTp/HNygra9gs=", BinaryEncoding.Base64),
Compression.Deflate
)
),
let _t = ((type nullable text) meta [Serialized.Text = true]) in
type table [
Year = _t, Month = _t, Product = _t, Gross_Sales = _t,
COGS = _t, Operating_Expenses = _t, Interest_Taxes = _t
]
),
/* Change column data types: */
ChangeType = Table.TransformColumnTypes(Source, {
{"Year", Int64.Type}, {"Month", type text}, {"Product", type text},
{"Gross_Sales", Int64.Type}, {"COGS", Int64.Type},
{"Operating_Expenses", Int64.Type}, {"Interest_Taxes", Int64.Type}
}),
/* Define a Temp Table with flags to skip or keep columns: */
tbl_ColumnFlags = #table(
{"Field_Name", "Skip_Flag"},
{
{"Year", true},
{"Month", true},
{"Product", true},
{"Gross_Sales", true},
{"COGS", false},
{"Operating_Expenses", false},
{"Interest_Taxes", false}
}
),
/* Get list of columns where Skip = false: */
Cols_List = Table.SelectRows(tbl_ColumnFlags, each [Skip_Flag] = false)[Field_Name],
/* Generate additional Percentage Columns */
Add_Ratios = List.Accumulate(
Cols_List,
ChangeType,
(i, c) => Table.AddColumn(
i,
"% " & c,
each if ([Gross_Sales] = null or [Gross_Sales] = 0) then null
else (Record.Field( _, c) / [Gross_Sales]),
type nullable Percentage.Type
)
)
in
Add_Ratios
--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------
