Skip to content

Instantly share code, notes, and snippets.

@martijnlentink

martijnlentink/ScriptDocumentation.md

Created October 24, 2019 13:29
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save martijnlentink/6e43d43f6ac34f71406ed7f729880540 to your computer and use it in GitHub Desktop.
Save martijnlentink/6e43d43f6ac34f71406ed7f729880540 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
ScriptDocumentation.md

Power Queries (M expressions)

NAME EXPRESSION
Fact let
Source = Sql.Database(".", "IP", [Query="SELECT #(lf) concat([GJAHR] , substring([PERDE], 2, 2) ) + 400 YearPeriod#(lf) ,[KNDNR] [Customer Key]#(lf) --,[ARTNR] [Product]#(lf) --,[FKART] [Billing Type]#(lf) --,[FRWAE] [Currency Key]#(lf) --,[VTWEG] [Distribution Channel]#(lf) ,[VTWEG] [Product Key]#(lf) --,[PRCTR] [Profit Center]#(lf) ,pc.[Profit Center Key] [BU Key]#(lf) ,1 [Scenario Key]#(lf) --,[KMMAKL] [Material Group]#(lf) ,[ERLOS]*1.20 [Revenue]#(lf) ,[VVMAT] [Material Costs]#(lf) ,[VVLCV] [Labor Costs Variable]#(lf) ,[VVN30] [Taxes]#(lf) ,[VV910] [Rev for Exp Travel]#(lf) ,[VV950] [Travel Expenses]#(lf) ,[VV970] [Cost Third Party]#(lf) ,[VVYSV] [Subscription Revenue]#(lf) FROM OSQ.[osq].[CE11010] f, mds.mdm.profitcenter pc#(lf)where [MANDT] = 210 and concat([GJAHR] , substring([PERDE], 2, 2) ) < 201012 and f.PRCTR = pc.PCName and [FRWAE] = 'USD'#(lf)union all #(lf)SELECT #(lf) concat([GJAHR] , substring([PERDE], 2, 2) ) + 400 PeriodYear#(lf) ,[KNDNR] [Customer]#(lf) --,[ARTNR] [Product]#(lf) --,[FKART] [Billing Type]#(lf) --,[FRWAE] [Currency Key]#(lf) --,[VTWEG] [Distribution Channel]#(lf) ,[VTWEG] [Product Key]#(lf) --,[PRCTR] [Profit Center]#(lf) ,pc.[Profit Center Key] [BU Key]#(lf) ,2 [Scenario Key]#(lf) --,[KMMAKL] [Material Group]#(lf) ,[ERLOS]*1.4 [Revenue]#(lf) ,[VVMAT] [Material Costs]#(lf) ,[VVLCV] [Labor Costs Variable]#(lf) ,[VVN30] [Taxes]#(lf) ,[VV910] [Rev for Exp Travel]#(lf) ,[VV950] [Travel Expenses]#(lf) ,[VV970] [Cost Third Party]#(lf) ,[VVYSV] [Subscription Revenue]#(lf) FROM OSQ.[osq].[CE11010] f tablesample (85 percent), mds.mdm.profitcenter pc#(lf)where [MANDT] = 300 and concat([GJAHR] , substring([PERDE], 2, 2) ) < 201012 and f.PRCTR = pc.PCName and [FRWAE] = 'USD'"]),
#"Renamed Columns" = Table.RenameColumns(Source, {{"Customer Key", "Customer Key"}, {"Product Key", "Product Key"}, {"BU Key", "BU Key"}, {"Scenario Key", "Scenario Key"}, {"Revenue", "Revenue"}, {"Material Costs", "Material Costs"}, {"Labor Costs Variable", "Labor Costs Variable"}, {"Taxes", "Taxes"}, {"Rev for Exp Travel", "Rev for Exp Travel"}, {"Travel Expenses", "Travel Expenses"}, {"Cost Third Party", "Cost Third Party"}, {"Subscription Revenue", "Subscription Revenue"}, {"YearPeriod", "YearPeriod"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns", {{"Customer Key", Int64.Type}, {"Product Key", type text}, {"BU Key", type number}, {"Scenario Key", Int64.Type}, {"Revenue", type number}, {"Material Costs", type number}, {"Labor Costs Variable", type number}, {"Taxes", type number}, {"Rev for Exp Travel", type number}, {"Travel Expenses", type number}, {"Cost Third Party", type number}, {"Subscription Revenue", type number}, {"YearPeriod", type text}})
in
#"Changed Type"
BU let
Source = Sql.Database(".", "IP", [Query="SELECT #(lf) #(lf) [Profit Center Key] [BU Key],#(lf) BU,#(lf) Division, Executive_id#(lf) FROM [MDS].[mdm].[profitcenter]"]),
#"Renamed Columns" = Table.RenameColumns(Source, {{"BU Key", "BU Key"}, {"BU", "BU"}, {"Division", "Division"}, {"Executive_id", "Executive_id"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns", {{"BU Key", type number}, {"BU", type text}, {"Division", type text}, {"Executive_id", Int64.Type}})
in
#"Changed Type"
Date let
Source = Sql.Database(".", "IP", [Query="SELECT#(lf) concat([Year], [period]) YearPeriod#(lf)#(tab) ,[Year]#(lf) ,[Period]#(lf) ,[Date]#(lf) ,[Month]#(lf) ,[QtrID]#(lf) ,[Qtr]#(lf) FROM [MDS].[mdm].[fiscalperiod]"]),
#"Renamed Columns" = Table.RenameColumns(Source, {{"YearPeriod", "YearPeriod"}, {"Year", "Year"}, {"Period", "Period"}, {"Date", "Date"}, {"Month", "Month"}, {"QtrID", "QtrID"}, {"Qtr", "Qtr"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns", {{"YearPeriod", type text}, {"Year", Int64.Type}, {"Period", type text}, {"Date", type datetime}, {"Month", type text}, {"QtrID", type text}, {"Qtr", type text}})
in
#"Changed Type"
Scenario let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJMLilNzFGK1YlWMgJynUpT0lNLlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate))),
#"Renamed Columns" = Table.RenameColumns(Source, {{"Column1", "Scenario Key"}, {"Column2", "Scenario"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns", {{"Scenario Key", Int64.Type}, {"Scenario", type text}})
in
#"Changed Type"
Product let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRQ0lEKzi9LVIrViVYyAvECijJzS4vBfGMQ3yW/KD8dIm8C4rvnJKZkQhVYgAR8PXXNjSBcI5B8aV5eapFSbCwA", BinaryEncoding.Base64), Compression.Deflate))),
#"Renamed Columns" = Table.RenameColumns(Source, {{"Column1", "Product Key"}, {"Column2", "Product"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns", {{"Product Key", Int64.Type}, {"Product", type text}})
in
#"Changed Type"
Customer let
Source = Excel.Workbook(File.Contents("C:\Users\mad\Dropbox\Work\Obvience\IP\Customer and Product Profitability\Data\dimCustomer.xlsx"), null, true),
Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet),
#"Renamed Columns" = Table.RenameColumns(#"Promoted Headers", {{"Customer", "Customer"}, {"Country", "Country/Region"}, {"Name", "Name"}, {"City", "City"}, {"Postal Code", "Postal Code"}, {"State", "State"}, {"Industry ID", "Industry ID"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns", {{"Customer", type number}, {"Country/Region", type text}, {"Name", type text}, {"City", type text}, {"Postal Code", type number}, {"State", type text}, {"Industry ID", type number}})
in
#"Changed Type"
Industry let
Source = Sql.Database(".", "IP", [Query="SELECT [dbo].[Industry].* FROM [dbo].[Industry]"]),
#"Renamed Columns" = Table.RenameColumns(Source, {{"ID", "ID"}, {"Industry", "Industry"}, {"Image", "Image"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns", {{"ID", Int64.Type}, {"Industry", type text}, {"Image", type binary}})
in
#"Changed Type"
Executive let
Source = Sql.Database(".", "IP", [Query="SELECT [dbo].[Executive].* FROM [dbo].[Executive]"]),
#"Renamed Columns" = Table.RenameColumns(Source, {{"ID", "ID"}, {"Name", "Name"}, {"Img", "Img"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns", {{"ID", Int64.Type}, {"Name", type text}, {"Img", type binary}})
in
#"Changed Type"
State let
Source = Sql.Database(".", "IP", [Query="select StateShort StateCode, StateName State, Region from MDS.mdm.state"]),
#"Renamed Columns" = Table.RenameColumns(Source, {{"StateCode", "StateCode"}, {"State", "State"}, {"Region", "Region"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns", {{"StateCode", type text}, {"State", type text}, {"Region", type text}})
in
#"Changed Type"

Measures

MEASURE_NAME CUBE_NAME MEASURE_DISPLAY_FOLDER NUMERIC_PRECISION MEASUREGROUP_NAME MEASURE_UNIQUE_NAME MEASURE_AGGREGATOR DATA_TYPE NUMERIC_SCALE EXPRESSION CATALOG_NAME DEFAULT_FORMAT_STRING MEASURE_UNQUALIFIED_CAPTION DESCRIPTION MEASURE_NAME_SQL_COLUMN_NAME MEASURE_IS_VISIBLE MEASURE_CAPTION
__Default measure Model 65535 [Measures].[__Default measure] 127 12 -1 1 Customer Profitability Sample PBIX __Default measure __Default measure false __Default measure
Total Revenue Model 65535 Fact [Measures].[Total Revenue] 0 5 -1 SUM([Revenue]) Customer Profitability Sample PBIX $#,0;($#,0);$#,0 Total Revenue Total Revenue true Total Revenue
Sum of Material Costs Model 65535 Fact [Measures].[Sum of Material Costs] 0 5 -1 SUM([Material Costs]) Customer Profitability Sample PBIX $#,0;($#,0);$#,0 Sum of Material Costs Sum of Material Costs true Sum of Material Costs
Sum of Labor Costs Variable Model 65535 Fact [Measures].[Sum of Labor Costs Variable] 0 5 -1 SUM([Labor Costs Variable]) Customer Profitability Sample PBIX $#,0;($#,0);$#,0 Sum of Labor Costs Variable Sum of Labor Costs Variable true Sum of Labor Costs Variable
Sum of Taxes Model 65535 Fact [Measures].[Sum of Taxes] 0 5 -1 SUM([Taxes]) Customer Profitability Sample PBIX $#,0;($#,0);$#,0 Sum of Taxes Sum of Taxes true Sum of Taxes
Sum of Rev for Exp Travel Model 65535 Fact [Measures].[Sum of Rev for Exp Travel] 0 5 -1 SUM([Rev for Exp Travel]) Customer Profitability Sample PBIX $#,0;($#,0);$#,0 Sum of Rev for Exp Travel Sum of Rev for Exp Travel true Sum of Rev for Exp Travel
Sum of Travel Expenses Model 65535 Fact [Measures].[Sum of Travel Expenses] 0 5 -1 SUM([Travel Expenses]) Customer Profitability Sample PBIX $#,0;($#,0);$#,0 Sum of Travel Expenses Sum of Travel Expenses true Sum of Travel Expenses
Sum of Cost Third Party Model 65535 Fact [Measures].[Sum of Cost Third Party] 0 5 -1 SUM([Cost Third Party]) Customer Profitability Sample PBIX $#,0;($#,0);$#,0 Sum of Cost Third Party Sum of Cost Third Party true Sum of Cost Third Party
Sum of Subscription Revenue Model 65535 Fact [Measures].[Sum of Subscription Revenue] 0 5 -1 SUM([Subscription Revenue]) Customer Profitability Sample PBIX $#,0;($#,0);$#,0 Sum of Subscription Revenue Sum of Subscription Revenue true Sum of Subscription Revenue
Gross Margin Model 65535 Fact [Measures].[Gross Margin] 0 5 -1 [Total Revenue]-[Total COGS] Customer Profitability Sample PBIX $#,0;($#,0);$#,0 Gross Margin Gross Margin true Gross Margin
GM% Model 65535 Fact [Measures].[GM%] 0 5 -1 DIVIDE([Gross Margin],[Total Revenue]) Customer Profitability Sample PBIX 0.0%;-0.0%;0.0% GM% GM% true GM%
YTD Revenue Model 65535 Fact [Measures].[YTD Revenue] 0 5 -1 TOTALYTD(SUM([Revenue]),'Date'[Date]) Customer Profitability Sample PBIX $#,0;($#,0);$#,0 YTD Revenue YTD Revenue true YTD Revenue
Total COGS Model 65535 Fact [Measures].[Total COGS] 0 5 -1 [Sum of Material Costs]+[Sum of Labor Costs Variable]+[Sum of Taxes]+[Sum of Rev for Exp Travel]+[Sum of Travel Expenses]+[Sum of Cost Third Party] Customer Profitability Sample PBIX $#,0;($#,0);$#,0 Total COGS Total COGS true Total COGS
YTD COGS Model 65535 Fact [Measures].[YTD COGS] 0 5 -1 TOTALYTD([Total COGS],'Date'[Date]) Customer Profitability Sample PBIX $#,0;($#,0);$#,0 YTD COGS YTD COGS true YTD COGS
YTD Gross Margin Model 65535 Fact [Measures].[YTD Gross Margin] 0 5 -1 TOTALYTD([Gross Margin],'Date'[Date]) Customer Profitability Sample PBIX $#,0;($#,0);$#,0 YTD Gross Margin YTD Gross Margin true YTD Gross Margin
Revenue SPLY Model 65535 Fact [Measures].[Revenue SPLY] 0 5 -1 CALCULATE([Total Revenue],SAMEPERIODLASTYEAR('Date'[Date])) Customer Profitability Sample PBIX $#,0;($#,0);$#,0 Revenue SPLY Revenue SPLY true Revenue SPLY
Gross Margin SPLY Model 65535 Fact [Measures].[Gross Margin SPLY] 0 5 -1 CALCULATE([Gross Margin],SAMEPERIODLASTYEAR('Date'[Date])) Customer Profitability Sample PBIX $#,0;($#,0);$#,0 Gross Margin SPLY Gross Margin SPLY true Gross Margin SPLY
GM% SPLY Model 65535 Fact [Measures].[GM% SPLY] 0 5 -1 [Gross Margin SPLY]/[Revenue SPLY] Customer Profitability Sample PBIX 0.0%;-0.0%;0.0% GM% SPLY GM% SPLY true GM% SPLY
YTD GM% Model 65535 Fact [Measures].[YTD GM%] 0 5 -1 [YTD Gross Margin]/[YTD Revenue] Customer Profitability Sample PBIX 0.0%;-0.0%;0.0% YTD GM% YTD GM% true YTD GM%
YTD Revenue SPLY Model 65535 Fact [Measures].[YTD Revenue SPLY] 0 5 -1 CALCULATE([YTD Revenue],SAMEPERIODLASTYEAR(DATESYTD('Date'[Date]))) Customer Profitability Sample PBIX $#,0;($#,0);$#,0 YTD Revenue SPLY YTD Revenue SPLY true YTD Revenue SPLY
COGS SPLY Model 65535 Fact [Measures].[COGS SPLY] 0 5 -1 CALCULATE([Total COGS],SAMEPERIODLASTYEAR('Date'[Date])) Customer Profitability Sample PBIX $#,0;($#,0);$#,0 COGS SPLY COGS SPLY true COGS SPLY
YTD COGS SPLY Model 65535 Fact [Measures].[YTD COGS SPLY] 0 5 -1 CALCULATE([YTD COGS],SAMEPERIODLASTYEAR(DATESYTD('Date'[Date]))) Customer Profitability Sample PBIX $#,0;($#,0);$#,0 YTD COGS SPLY YTD COGS SPLY true YTD COGS SPLY
YTD GM% SPLY Model 65535 Fact [Measures].[YTD GM% SPLY] 0 5 -1 [YTD GM SPLY]/[YTD Revenue SPLY] Customer Profitability Sample PBIX 0.0%;-0.0%;0.0% YTD GM% SPLY YTD GM% SPLY true YTD GM% SPLY
YTD GM SPLY Model 65535 Fact [Measures].[YTD GM SPLY] 0 5 -1 CALCULATE([YTD Gross Margin],SAMEPERIODLASTYEAR(DATESYTD('Date'[Date]))) Customer Profitability Sample PBIX $#,0;($#,0);$#,0 YTD GM SPLY YTD GM SPLY true YTD GM SPLY
YoY Rev Var Model 65535 Fact [Measures].[YoY Rev Var] 0 5 -1 [Total Revenue]-[Revenue SPLY] Customer Profitability Sample PBIX $#,0;($#,0);$#,0 YoY Rev Var YoY Rev Var true YoY Rev Var
YoY GM Var Model 65535 Fact [Measures].[YoY GM Var] 0 5 -1 [Gross Margin]-[Gross Margin SPLY] Customer Profitability Sample PBIX $#,0;($#,0);$#,0 YoY GM Var YoY GM Var true YoY GM Var
YoY Rev Growth Model 65535 Fact [Measures].[YoY Rev Growth] 0 5 -1 IF([Revenue SPLY],[YoY Rev Var]/[Revenue SPLY],BLANK()) Customer Profitability Sample PBIX 0.0%;-0.0%;0.0% YoY Rev Growth YoY Rev Growth true YoY Rev Growth
YoY GM Growth Model 65535 Fact [Measures].[YoY GM Growth] 0 5 -1 IF([Gross Margin SPLY],[YoY GM Var]/[Gross Margin SPLY],BLANK()) Customer Profitability Sample PBIX 0.0%;-0.0%;0.0% YoY GM Growth YoY GM Growth true YoY GM Growth
YoY YTD Rev Var Model 65535 Fact [Measures].[YoY YTD Rev Var] 0 5 -1 [YTD Revenue]-[YTD Revenue SPLY] Customer Profitability Sample PBIX $#,0;($#,0);$#,0 YoY YTD Rev Var YoY YTD Rev Var true YoY YTD Rev Var
YoY COGS Var Model 65535 Fact [Measures].[YoY COGS Var] 0 5 -1 [Total COGS]-[COGS SPLY] Customer Profitability Sample PBIX $#,0;($#,0);$#,0 YoY COGS Var YoY COGS Var true YoY COGS Var
YoY YTD COGS Var Model 65535 Fact [Measures].[YoY YTD COGS Var] 0 5 -1 [YTD COGS]-[YTD COGS SPLY] Customer Profitability Sample PBIX $#,0;($#,0);$#,0 YoY YTD COGS Var YoY YTD COGS Var true YoY YTD COGS Var
YoY YTD GM% Var Model 65535 Fact [Measures].[YoY YTD GM% Var] 0 5 -1 [YTD GM%]-[YTD GM% SPLY] Customer Profitability Sample PBIX #,0 YoY YTD GM% Var YoY YTD GM% Var true YoY YTD GM% Var
YoY YTD GM Var Model 65535 Fact [Measures].[YoY YTD GM Var] 0 5 -1 [YTD Gross Margin]-[YTD GM SPLY] Customer Profitability Sample PBIX $#,0;($#,0);$#,0 YoY YTD GM Var YoY YTD GM Var true YoY YTD GM Var
YoY GM %Var Model 65535 Fact [Measures].[YoY GM %Var] 0 5 -1 [GM%]-[GM% SPLY] Customer Profitability Sample PBIX YoY GM %Var YoY GM %Var true YoY GM %Var
YoY YTD Rev Growth Model 65535 Fact [Measures].[YoY YTD Rev Growth] 0 5 -1 IF([YTD Revenue SPLY],[YoY YTD Rev Var]/[YTD Revenue SPLY],BLANK()) Customer Profitability Sample PBIX 0.0%;-0.0%;0.0% YoY YTD Rev Growth YoY YTD Rev Growth true YoY YTD Rev Growth
YoY COGS Growth Model 65535 Fact [Measures].[YoY COGS Growth] 0 5 -1 IF([COGS SPLY],[YoY COGS Var]/[COGS SPLY],BLANK()) Customer Profitability Sample PBIX 0.0%;-0.0%;0.0% YoY COGS Growth YoY COGS Growth true YoY COGS Growth
YoY YTD COGS Growth Model 65535 Fact [Measures].[YoY YTD COGS Growth] 0 5 -1 IF([YTD COGS SPLY],[YoY YTD COGS Var]/[YTD COGS SPLY],BLANK()) Customer Profitability Sample PBIX 0.0%;-0.0%;0.0% YoY YTD COGS Growth YoY YTD COGS Growth true YoY YTD COGS Growth
YoY YTD GM Growth Model 65535 Fact [Measures].[YoY YTD GM Growth] 0 5 -1 IF([YTD GM SPLY],[YoY YTD GM Var]/[YTD GM SPLY],BLANK()) Customer Profitability Sample PBIX 0.0%;-0.0%;0.0% YoY YTD GM Growth YoY YTD GM Growth true YoY YTD GM Growth
# of Customers Model 65535 Fact [Measures].[# of Customers] 0 20 -1 DISTINCTCOUNT([Customer Key]) Customer Profitability Sample PBIX # of Customers # of Customers true # of Customers
# of Products Model 65535 Fact [Measures].[# of Products] 0 20 -1 DISTINCTCOUNT([Product Key]) Customer Profitability Sample PBIX # of Products # of Products true # of Products
Revenue Budget Model 65535 Fact [Measures].[Revenue Budget] 0 5 -1 CALCULATE([Total Revenue], FILTER(Scenario, Scenario[Scenario]="Budget")) Customer Profitability Sample PBIX $#,0;($#,0);$#,0 Revenue Budget Revenue Budget true Revenue Budget
Revenue Var to Budget Model 65535 Fact [Measures].[Revenue Var to Budget] 0 5 -1 [RevenueTY]-[Revenue Budget] Customer Profitability Sample PBIX $#,0;($#,0);$#,0 Revenue Var to Budget Revenue Var to Budget true Revenue Var to Budget
Revenue Var % to Budget Model 65535 Fact [Measures].[Revenue Var % to Budget] 0 5 -1 DIVIDE([Revenue Var to Budget], [RevenueTY]) Customer Profitability Sample PBIX 0.00%;-0.00%;0.00% Revenue Var % to Budget Revenue Var % to Budget true Revenue Var % to Budget
RevenueTY Model 65535 Fact [Measures].[RevenueTY] 0 5 -1 CALCULATE([Total Revenue], FILTER(Scenario, Scenario[Scenario]="Actual")) Customer Profitability Sample PBIX $#,0;($#,0);$#,0 RevenueTY RevenueTY true RevenueTY
_RevenueTY Goal Model 65535 Fact [Measures].[_RevenueTY Goal] 0 5 -1 'Fact'[Revenue Budget] Customer Profitability Sample PBIX $#,0;($#,0);$#,0 _RevenueTY Goal _RevenueTY Goal false _RevenueTY Goal
_RevenueTY Status Model 65535 Fact [Measures].[_RevenueTY Status] 0 20 -1 var x='Fact'[RevenueTY]/'Fact'[_RevenueTY Goal] return
if(ISBLANK(x),BLANK(),
If(x<0.95,-1,
If(x<1,0,1)
)
) 		Customer Profitability Sample PBIX _RevenueTY Status _RevenueTY Status false _RevenueTY Status
Sum of Revenue Model 65535 Fact [Measures].[Sum of Revenue] 0 5 -1 SUM('Fact'[Revenue]) Customer Profitability Sample PBIX Sum of Revenue Sum of Revenue true Sum of Revenue
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment