Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a situation like below. I want the Status values from Table B for Table A. Common column is Status ID. But in Table B I have 3 values for Status ID = 1.
I usually use LookUp --> Lookup(Table B.Name, Table A.Status ID, Table B.Status ID,""). How can I do it if I want all the 3 values from Table B. Yes it obviously makes Table A's one row in to 3 rows. How can we achieve this..?
Solved! Go to Solution.
If you only want to get first value from each Status ID, you can add an Index column in Power Query.
My Sample:
Table A
Table B
Firstly Group all rows in Table B by [Status ID].
Add Index M code in Advance Editor:
Indexed = Table.TransformColumns(#"Grouped Rows", {{"Row", each Table.AddIndexColumn(_,"GroupIndex", 1, 1)}})
The whole M code is as below.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfIvSM1TitWBcJxz8otT4Ty3zIrUFDDPCFmhEUJhLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Status ID" = _t, Name = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Status ID", Int64.Type}, {"Name", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Status ID"}, {{"Row", each _, type table [Status ID=nullable number, Name=nullable text]}})
,Indexed = Table.TransformColumns(#"Grouped Rows", {{"Row", each Table.AddIndexColumn(_,"GroupIndex", 1, 1)}}),
#"Expanded Row" = Table.ExpandTableColumn(Indexed, "Row", {"Name", "GroupIndex"}, {"Row.Name", "Row.GroupIndex"})
in
#"Expanded Row"
New Table B:
Merge two tables and filter index =1.
Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If you only want to get first value from each Status ID, you can add an Index column in Power Query.
My Sample:
Table A
Table B
Firstly Group all rows in Table B by [Status ID].
Add Index M code in Advance Editor:
Indexed = Table.TransformColumns(#"Grouped Rows", {{"Row", each Table.AddIndexColumn(_,"GroupIndex", 1, 1)}})
The whole M code is as below.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfIvSM1TitWBcJxz8otT4Ty3zIrUFDDPCFmhEUJhLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Status ID" = _t, Name = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Status ID", Int64.Type}, {"Name", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Status ID"}, {{"Row", each _, type table [Status ID=nullable number, Name=nullable text]}})
,Indexed = Table.TransformColumns(#"Grouped Rows", {{"Row", each Table.AddIndexColumn(_,"GroupIndex", 1, 1)}}),
#"Expanded Row" = Table.ExpandTableColumn(Indexed, "Row", {"Name", "GroupIndex"}, {"Row.Name", "Row.GroupIndex"})
in
#"Expanded Row"
New Table B:
Merge two tables and filter index =1.
Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @BI_Analyticz ,
You will have to create a join between these 2 tables in Power Query Editor. See the blog below:
https://radacad.com/how-to-change-joining-types-in-power-bi-and-power-query
Thanks,
Pragati
Hi @BI_Analyticz ,
To get just the first value, you can use FIRSTNONBLANK dax function. See the details below:
https://docs.microsoft.com/en-us/dax/firstnonblank-function-dax
Thanks,
Pragati
I am unable to use FIRSTNONBLANK in Merge or in Lookup
Hi @BI_Analyticz ,
You don't use FIRSTNONBLANK with LOOKUP function.
Check the following thread on how to use this function:
https://community.powerbi.com/t5/Desktop/Lookupvalue-only-first-result/m-p/244009
Thanks,
Pragati
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
77 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |