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

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

sashaxiv — Fri, 12 Apr 2024 07:21:24 GMT

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!

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

lbendlin — Fri, 12 Apr 2024 11:53:37 GMT

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

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

sashaxiv — Fri, 12 Apr 2024 12:29:45 GMT

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

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

lbendlin — Fri, 12 Apr 2024 13:17:14 GMT

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

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

Anonymous — Mon, 15 Apr 2024 06:24:21 GMT

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.