Forum Discussion
Consolidating multiple data items into fewr choices
Hello PBi hive brain,
I am populating a SP list using MS Forms, part of the form records the reporters' department from a fixed list choice, when this is brouhght into PBI I want to relate each department into it's over-arching Section. The relationship between the two is shown in the table below I have in my pbi.
My question is how do I populate a new column in the main table using the below table as a key/relationship defintiion? i.e. Everytime the DEPT = Business Improvement, the new column would show "Other", whenever DEPT = Cabin Maintenance, the new column would show "Part-145" etc.
Many Thanks
kierenjblake Hi! You can do this with a merge.
1. In Power Query:
Go to Home > Merge QueriesSelect your Form responses table as the primary table.
Select your Mapping table as the secondary table.
Join them on the Department column.
Use a Left Outer Join (keep all from Form responses, matching from Mapping table)
2. Expand the Section:
After merging, click the expand icon on the new column (from your Mapping table)Select only the Section column to add.
Rename it to something clear like Department Section
3. Close & Apply:
Load the data back into Power BI and you're set.If you paste the advanced editors of the tables, i can fix your code.
BBF
💡 Did I answer your question? Mark my post as a solution!
👍 Kudos are appreciated
🔥 Proud to be a Super User!
4 Replies
kierenjblake Hi! You can do this with a merge.
1. In Power Query:
Go to Home > Merge QueriesSelect your Form responses table as the primary table.
Select your Mapping table as the secondary table.
Join them on the Department column.
Use a Left Outer Join (keep all from Form responses, matching from Mapping table)
2. Expand the Section:
After merging, click the expand icon on the new column (from your Mapping table)Select only the Section column to add.
Rename it to something clear like Department Section
3. Close & Apply:
Load the data back into Power BI and you're set.If you paste the advanced editors of the tables, i can fix your code.
BBF
💡 Did I answer your question? Mark my post as a solution!
👍 Kudos are appreciated
🔥 Proud to be a Super User!
Thanks! I'll give this a try and let you know how I get on!
Hello kierenjblake - you can achieve this by creating a calculated column in Power Query. This can be done in a few different ways, which I have added below.
Add a conditional column via the UI and build your If/then/else conditions.
Add a custom column via the UI and add your if/then/else code. You can also do this in the Advanced Editor if you prefer a free-hand code experience.
Add column from examples, select the DEPT column and start typing some examples of the expected result in the column cells. The query editor will try to figure out the logic needed and will add it for you.
Please let me know if you have any other questions.
Thanks jennratten I've got what I needed from BeaBF, but will have a play with your idea too, thanks!
