Excel and PowerQuery

[Ankushbhatia]

Hi All,

I am learning power query and stuck on one thing

i imported data using excel and created one to many relationship

table 1

prod id, prod name

P1, sample

p2, example

table 2

prod id, module id,module name, module status  

P1, m1,load data, production

P1, m2, modify data, retired 

P1, m3, verify data, in development 

P2, m4, load, Retired

P2, m5, verify, Retired 

now, I would like to have a column in table 1 which picks the status of product based on table 2 module status column

if any module status is production or in development then product is active otherwise retired ...

any clues ?

[slorin · Accepted solution]

Hi @Ankushbhatia 

let
Table1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Table2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
Join = Table.NestedJoin(Table1, {"prod id"}, Table2, {"prod id"}, "Table2", JoinKind.LeftOuter),
#"Active/Retired" = Table.AddColumn(Join, "Active/Retired", 
   each if List.ContainsAny([Table2][module status], {"production","in development"}) then "active" else "retired")
in
#"Active/Retired"

Stéphane 

[Omid_Motamedise]

You can use merge queries, so go to the table 1 and from Home tab pick Merge Queries and then in the opened window in the below part select the second table and chose the common columns between the tables and press ok

[slorin · Accepted solution]

Hi @Ankushbhatia 

let
Table1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Table2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
Join = Table.NestedJoin(Table1, {"prod id"}, Table2, {"prod id"}, "Table2", JoinKind.LeftOuter),
#"Active/Retired" = Table.AddColumn(Join, "Active/Retired", 
   each if List.ContainsAny([Table2][module status], {"production","in development"}) then "active" else "retired")
in
#"Active/Retired"

Stéphane 

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information. Do not include anything that is unrelated to the issue or question.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[Ankushbhatia]

Hi, I have these 2 tables which I am referring 2

i am trying to achieve 2 things 

a. Get the product status if any module is still active 

b. Validate if any module is missing in product table and highlight that 

hence I was trying to use power query to do that ..loaded the data and created one to many relationship..and then looking for guidance from experts here

 

[Anonymous · Accepted solution]

Hi @Ankushbhatia ，

There is no highlighting feature in PowerQuery, you can add a custom column to find which modules are missing。

I attached a pbix file for your reference.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum