Custom columns in Power Query

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.

 

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