Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Happy New Year Power BI aficionados
So I have a data set imported into Power BI (via a database source) that looks as below:
Col1 | Col2 | Col3 | Col4 | Col5 |
Col1Value | Col2Value | null | null | null |
Col1Value | null | Col3Value | null | null |
Col1Value | null | null | Col4Value | null |
Col1Value | null | null | null | Col5Value |
(Each 'Col1Value' value within the column Col1 is same in all the rows)
What I'm trying to achieve is to have a table on my Power BI report that displays this information as below (i.e. basically I want to merge all the rows and eliminate all the nulls on Col2 to Col5 so there's a single record for Col1Value. Hope that makes sense?)
Columns Col2, Col3, Col4 and Col5 are of type text
Col1 | Col2 | Col3 | Col4 | Col5 |
Col1Value | Col2Value | Col3Value | Col4Value | Col5Value |
Is this possible to do this via Power Query transformations/DAX?
Let me know if this is unclear
Thanks in advance for your help
Solved! Go to Solution.
Hi @Anonymous ,
you can use Power Query with Group By
Regards,
Marcus
Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.
Hi @Anonymous ,
I created a calculated table:
Table 2 =
SUMMARIZE(
Sheet5,
Sheet5[Col1],
"Col2", MIN(Sheet5[Col2]),
"Col3", MIN(Sheet5[Col3]),
"Col4", MIN(Sheet5[Col4]),
"Col5", MIN(Sheet5[Col5])
)
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous
is your problem solved?
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.
Hi,
Assuming that the null are actually blank cells, try these transformation steps in the Query Editor:
Hope this helps.
Hi @Anonymous ,
you can use Power Query with Group By
Regards,
Marcus
Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.
In DAX you can Use summarize and firstnonblank/min/max to group the data
https://docs.microsoft.com/en-us/dax/summarize-function-dax
https://docs.microsoft.com/en-us/dax/firstnonblank-function-dax
New table = summarize(col1, "col2", min(col2), "col3", min(col3), "col4", min(col4))
Or
New table = summarize(col1, "col2", firstnonblank(col2), "col3", firstnonblank(col3), "col4", firstnonblank(col4))
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601
User | Count |
---|---|
140 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |