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
--------------------------------------------------------------------------------------------------------
No comments:
Post a Comment
Hi User, Thank You for visiting My Blog. If you wish, please share your genuine Feedback or comments only related to this Blog Posts. It is my humble request that, please do not post any Spam comments or Advertising kind of comments, which will be Ignored.