Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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!
Solved! Go to 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.
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.
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
Unpivot it as part of your Power Query script, so it always operates on the latest data.
Unpivot table B to bring it into a usable format. Then you can join directly via your key.
User | Count |
---|---|
22 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
26 | |
13 | |
11 | |
9 | |
6 |