Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi
I need to either fill the blank values in the collumn "module ID" with the same number as the one that are filled or create a new collumn as shown in the picture.
In the below picture is is quite clear that the module name "Getoveralltopselling" has module ID 65, but because of bad data collection some of the rows with that module has not noted the module ID (This module is not the only case - There are more than 100 rows which has a module name but no ID, but some other row has that same module name but with an ID)
How do I tell Power BI to return the value the correct value in the module ID row (In this example 65)?
I have tried lookupvalue, but i cannot return 65 because the module name and the number 65 appears multiple times.
Afterwards I have another question.
I need to create a table with a collumn of all distinct values of the modules and another collumn with the specific modules ID.
Such that the first row would be
Module name - Module ID
Getoveralltopselling - 65
module 2 - 41
Module 3 - 32
Solved! Go to Solution.
Hi @Anonymous
I have done it other way around.
First, I created a reference table from the main table in Power Query:
Then, I removed duplicates post filtering out nulls
Then loaded the tables in Power BI and created relationship:
Used DAX to include the blank values
You may find the the sample pbix file here
Hope this solves the purpose!
Regards,
Vivek
If it helps, please mark it as a solution
Kudos would be a cherry on the top 🙂
https://www.vivran.in/
@Anonymous
Hi, you can accomplish this easily.
You can start by creating the calculated table with distinct values and based on that, lookup the unique id for each module to fill in the gaps in your main table.
Code for calculated table:
Table =
FILTER(
SUMMARIZE( MyTable,
MyTable[Module name],
MyTable[Module ID]
),
NOT ISBLANK( MyTable[Module ID] )
)
Code for ModuleID column:
Module ID Fix = LOOKUPVALUE('Table'[Module ID], 'Table'[Module name], MyTable[Module name])
Kind regards,
Razwan
Hi @Anonymous
I have done it other way around.
First, I created a reference table from the main table in Power Query:
Then, I removed duplicates post filtering out nulls
Then loaded the tables in Power BI and created relationship:
Used DAX to include the blank values
You may find the the sample pbix file here
Hope this solves the purpose!
Regards,
Vivek
If it helps, please mark it as a solution
Kudos would be a cherry on the top 🙂
https://www.vivran.in/
Hi Vivran22
Sorry for the late reply, but thank you very much!
It worked like a charm!
Kind regards
Junior-BI-Assi
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
114 | |
95 | |
86 | |
78 | |
66 |
User | Count |
---|---|
151 | |
121 | |
111 | |
107 | |
95 |