Put a dax formula in M

[AxelKAp]

Hello everyone,

I have created a DAX formule to create a new column but I want to create the column directly in Power Query thanks to M.
But I'm not an expert with M, so I need your help !
Below the formula in DAX:  

Final PO Type =

var _POnb = 'pochannel-mastervendor'[PO Number (Header)]

var _list = FILTER('pochannel-mastervendor', 'pochannel-mastervendor'[PO Number (Header)] = _POnb)

Return

IF(CONTAINS(_list,'pochannel-mastervendor'[PO Type] , "S2P"), "S2P", 'pochannel-mastervendor'[PO Type])

The formula help me to choose the correct PO Type, for example if I have 3 PO lines, 2 are P2P and 1 is S2P so I want to have the 3 lines in S2P.

Thanks in advance for your help ! 

[Vera_33 · Accepted solution]

Hi @AxelKAp 

There are different ways to do it, but it depends on your data. So it is better you can provide some sample data in a format which people can copy.

For example, the same PO number can contain P2P and S2P, but can it contain some other different values without S2P?

One way to group by PO number, check whether it contains S2P and merge back with the original table

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQowClCK1cFkB0PZRkjiuNjGeNglqcUlSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"PO Number" = _t, #"PO Type" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"PO Number", Int64.Type}, {"PO Type", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"PO Number"}, {{"new", each if List.Contains(_[PO Type],"S2P") then "S2P" else null }}),
    #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([new] = "S2P")),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"PO Number"}, #"Filtered Rows", {"PO Number"}, "S2P", JoinKind.LeftOuter),
    #"Added Custom" = Table.AddColumn(#"Merged Queries", "new", each try [S2P][new]{0} otherwise [PO Type]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"S2P"})
in
    #"Removed Columns"