DAX/PowerQuery Check if value is contained in 1-N list of values in another table

[sashaxiv]

Good morning all!

I would like to know the best way to deal with this problem.

I need to check for each value (ID_TASK_ORDER) in table A (ID-ID) , if it is contained in any posible combination in table B (ID- {List of ID's})

I was thinking of firstly Summarizing tabla B and then relate this new table and table A.

Do you think this is the way to solve the problem? 

How could I create with DAX the summary table with all distinct values?

Thanks in advance!

[Anonymous · Accepted solution]

Hi @sashaxiv ,

Thanks @lbendlin  for the quick reply. I have some other thoughts to add:

(1) In power query, copy a table B. Click "Advanced Editor" to copy and paste the following code.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUap21HGqVYrVgfOcITwjIM8VzDIGstyUYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID_TASK = _t, ORDERS = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID_TASK", Int64.Type}, {"ORDERS", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "ORDERS", Splitter.SplitTextByDelimiter("{", QuoteStyle.Csv), {"ORDERS.1", "ORDERS.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"ORDERS.1", type text}, {"ORDERS.2", type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "ORDERS.2", Splitter.SplitTextByDelimiter("}", QuoteStyle.Csv), {"ORDERS.2.1", "ORDERS.2.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"ORDERS.2.1", type text}, {"ORDERS.2.2", type text}}),
    #"Merged Columns" = Table.CombineColumns(#"Changed Type2",{"ORDERS.1", "ORDERS.2.1", "ORDERS.2.2"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),
    #"Renamed Columns" = Table.RenameColumns(#"Merged Columns",{{"Merged", "ORDERS"}}),
    #"Split Column by Delimiter2" = Table.ExpandListColumn(Table.TransformColumns(#"Renamed Columns", {{"ORDERS", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "ORDERS"),
    #"Removed Duplicates" = Table.Distinct(#"Split Column by Delimiter2", {"ORDERS"})
in
    #"Removed Duplicates"

Then the result is as follows.

We can create a table in desktop.

Table C = SUMMARIZE('Table B (2)','Table B (2)'[ID_TASK],"ORDERS",CONCATENATEX ( 'Table B (2)' , [ORDERS] , ","))

We can create a column in Table A.

IS_A_IN_B = 
var _table= SELECTCOLUMNS(ADDCOLUMNS('Table B (2)',"task_order",[ID_TASK] &"-" & [ORDERS]),"i_t_o",[task_order])
RETURN IF([ID_TASK_ORDER] in _table,1,0)

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[sashaxiv]

Yes, but I could have a problem.

Column Order in table B may have 1 to N different values (the table will be connected to an Excel file). If I unpivot table B with powerquery, let's imagine that the first time N=4 (orders A,B,C,D), but a couple of days the file contains 5 orders in a file N=5 (orders A...E).

Once the table is updated I will be missing order E

[lbendlin]

Unpivot it as part of your Power Query script, so it always operates on the latest data.

[lbendlin]

Unpivot table B to bring it into a usable format. Then you can join directly via your key.