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.
Hello!
I am running into this issue where I have this table with several numbers and need them to be lookedup in another table. Essentially, what I have is a couple tables like the following:
Main Table
Name | Value 1 | Value 2 | ... | Value 3 |
Bob | 5 | 4 | ... | 7 |
Joe | 4 | 4 | ... | 5 |
Steve | 1 | 2 | ... | 3 |
Lookup Table
ID | String |
1 | asdf |
2 | hjkl |
3 | qwer |
4 | fghj |
5 | 1234 |
6 | ???? |
7 | @!n1 |
I'm then wanting to merge these into a table like the one below
Name | Value 1 | Value 2 | ... | Value 3 |
Bob | 1234 | fghj | ... | @!n1 |
Joe | fghj | fghj | ... | 1234 |
Steve | asdf | hjkl | ... | qwer |
Any ideas how to do this sort of thing?
Solved! Go to Solution.
1) check column "Name"
2) Unpivot other columns
3) Merge on new column "Value" and expand
4) Consider if this table format isn't better suited for your data model than your original idea
optional 5) If you still want your old format, check column "Attribute" and pivot back on column "Value" (don't aggregate)
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
@Anonymous ,
I would copy your table, then use LOOKUPVALUE() to populate the new 3 columns, then delete the columns from the copied table except for name.
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel
Proud to be a Super User!
The issue with doing it that was is that there are 200+ columns in the real table. I was wondering if there was a more algorithmic solution
@Anonymous , Lets reach out to @ImkeF who is the master of algorithms!
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel
Proud to be a Super User!
Ok awesome!
As of right now, I have found the only good way to do this was to use Excel to generate the Advanced Editor code. As you can see from this screenshot of the query settings, it's very messy
1) check column "Name"
2) Unpivot other columns
3) Merge on new column "Value" and expand
4) Consider if this table format isn't better suited for your data model than your original idea
optional 5) If you still want your old format, check column "Attribute" and pivot back on column "Value" (don't aggregate)
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Ah that worked perfectly!
I totally missed the "dont aggregate" option and was getting stuck on that!
Thank you both so much!
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 |
---|---|
111 | |
94 | |
83 | |
67 | |
59 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |