Solved: Custom columns in Power Query

Anonymous · ‎03-30-2020

HI

At the moment below are dax-functions and it give me so many problems.
Can someone help me to transform these dax functions to columns in Power query? I need to transform below functions to Power query language.

Function 1: Latest date of buy= CALCULATE(MAX(FactVareposter[Entrydate]);FILTER(FactVareposter;FactVareposter[Entrytype]="Purchase"))

Function 2: Latest Consumption date = CALCULATE(MAX(FactVareposter[Entrydate]);FILTER(FactVareposter;FactVareposter[Sourcetype] in {"Customer";"Item";"Vendor"});FILTER(FactVareposter;FactVareposter[Entrytype]in {"Consumption";"Sale";"Negative adjmt."}))

Function 3: Days without sale = IF(or([Latestconsumptiondate]<[Latestdateofbuy];[Latestconsumptiondate]=BLANK());DATEDIFF([Latestdateofbuy];NOW();DAY);DATEDIFF([Latestconsumptiondate];NOW();DAY))

Thank you.

Best regards

Rune Iversen

v-frfei-msft · ‎03-30-2020

Hi @Anonymous ,

Does that meet your requirement?

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZDBC4IwFIf/ldhZ0DdN6+zJSwRBF/Ew8lFG28Jt/f0pNdDHzG6y9+P75KtrBjHEPOEJi9jR9ZebMDh8nlG1umdNNA64HxzwKmz3wo1o79IOD5VF+R2lfnQSj5FQOmO1RM/I/LnUyjj5tJ1W1LMliAk9/00vyHmG3YXVE/o+vCASSNZCQSjlxAM8cKeWdNWSLXcC2pDS89UYUPxXYyHr/FcXyn50zRs=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Entrydate = _t, Entrytype = _t, Sourcetype = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Entrydate", type date}, {"Entrytype", type text}, {"Sourcetype", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Entrytype"}, {{"MAXdate", each List.Max([Entrydate]), type date}, {"a", each _, type table [Entrydate=date, Entrytype=text, Sourcetype=text]}}),
    #"Expanded a" = Table.ExpandTableColumn(#"Grouped Rows", "a", {"Entrydate", "Sourcetype"}, {"a.Entrydate", "a.Sourcetype"}),
    #"Added Custom" = Table.AddColumn(#"Expanded a", "Custom", each if [Entrytype] = "Purchase" then [MAXdate] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
    #"Renamed Columns" = Table.RenameColumns(#"Filled Down",{{"Custom", "Latest date of buy"}}),
    #"Merged Queries" = Table.NestedJoin(#"Renamed Columns", {"Entrytype", "a.Sourcetype", "a.Entrydate"}, #"FactVareposter (2)", {"Entrytype", "Sourcetype", "Entrydate"}, "FactVareposter (2)", JoinKind.LeftOuter),
    #"Expanded FactVareposter (2)" = Table.ExpandTableColumn(#"Merged Queries", "FactVareposter (2)", {"Latest Consumption date"}, {"FactVareposter (2).Latest Consumption date"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded FactVareposter (2)", "Days without sale", each if [#"FactVareposter (2).Latest Consumption date"]<[Latest date of buy] or [#"FactVareposter (2).Latest Consumption date"] = null then [Latest date of buy]-DateTime.Date(DateTime.FixedLocalNow()) else [#"FactVareposter (2).Latest Consumption date"]-DateTime.Date(DateTime.FixedLocalNow())),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"MAXdate"})
in
    #"Removed Columns"

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZDBCoMwEER/peQs6Ear9ezJSykUehEPoS6tpUmKif3+KjQQl1h7CzvDm/CahkEMMU94wiJ2GofrXRicnhdUnR5YG80F7gpHvAnbv3Enuoe006G2KL+l1JXO4jkTqtFYLdExMhdXWplRvmyvFd3ZE4RHz3/TCxIvsIfwtEcvww0yAsmWKAip9HaAB3K6km6uZOuegDqk9HxTBhT/2VjRuvxquSqk/QA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Entrydate = _t, Entrytype = _t, Sourcetype = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Entrydate", type date}, {"Entrytype", type text}, {"Sourcetype", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Entrytype] <> "Purchase")),
    #"Changed Type1" = Table.TransformColumnTypes(#"Filtered Rows",{{"Entrydate", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each List.Max(#"Changed Type"[Entrydate])),
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"Custom", "Latest Consumption date"}}),
    #"Added Custom1" = Table.AddColumn(#"Renamed Columns", "Custom", each DateTime.Date(DateTime.FixedLocalNow())),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Custom"})
in
    #"Removed Columns"

If it doesn't meet your requirement, kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive for Business and share the link here.

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

Anonymous · ‎03-31-2020

Hi @v-frfei-msft and @Anonymous

@Anonymous , the use/behaviour of the DAX function is a bit different to M-type code use, so you need to make sure you understand what you are trying to implement. E.g. DAX result depends on slicers/filters setting in a given moment of time, this is quite tricky to implement in M (#3 formula is Ok, it is a calculated column rather than a measure in DAX terms).

So I've done it in a slightly different manner to what I would usually do, implemented as functions, so you can get close to what you would have in DAX.

//Latest_date_of_buy
(pTable as table)=>
let
    Source = pTable,
    #"Filtered Rows" = Table.SelectRows(Source, each ([Entrytype] = "Purchase")),
    #"Calculated Count" = List.Max(#"Filtered Rows"[Entrydate])
in
    #"Calculated Count"

//Latest_Consumption_date
(pTable as table)=>
let
    Source = pTable,
    #"Filtered Rows" = Table.SelectRows(Source, each List.Contains({"Consumption", "Sale", "Negative adjmt."} , [Entrytype]) and List.Contains({"Customer", "Item", "Vendor"}, [Sourcetype])),
    #"Calculated Count1" = List.Max(#"Filtered Rows"[Entrydate])
in
    #"Calculated Count1"

This is a neater form of Days Without Sale formula:

//Days_without_sale_A
(pTable as table) =>
let
    Source = pTable,
    #"Added Custom" = Table.AddColumn(Source, "Days without sale", each Duration.Days (Date.From(DateTime.LocalNow ()) -  List.Max({[Latestconsumptiondate], [Latestdateofbuy]})))
in
    #"Added Custom"

This is how to use it in M context (click on each step in PBI to see what it does, it is not "chained"):

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7PKy7NLSjJzM9T0lEy1DfUNzIAMpxLi0vyc1OLkMWMIYxYHXRdQORZkpqLQ21wYk4qRBEBQ/1S0xNLMstSFRJTsnJL9FAMJkJlWGpeSj7YbAMshgeUFiVnJBaDXGIEkyZaB6rbkZTFAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Entrytype = _t, Entrydate = _t, Sourcetype = _t, Latestconsumptiondate = _t, Latestdateofbuy = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Entrytype", type text}, {"Entrydate", type date}, {"Sourcetype", type text}, {"Latestconsumptiondate", type date}, {"Latestdateofbuy", type date}}),
    
    Group = Table.Group(#"Changed Type", {"Entrytype"}, {{"Latest date of buy", Latest_date_of_buy, type date}, {"Latest Consumption date", Latest_Consumption_date, type date}}),
    Scalar = Latest_date_of_buy(#"Changed Type"),
    DaysWithoutSale = Days_without_sale_A (#"Changed Type")

in
    DaysWithoutSale

Kind regards,

JB