Forum Discussion
Excel and PowerQuery
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 ?
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
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 TeamIf 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
5 Replies
slorinSuper User
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-Forum/ba-p/963216
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/1447523Hi, 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
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 TeamIf 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
- 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
