Forum Discussion
Help needed to merge rows via Power Query/Power BI DAX
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
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.
5 Replies
- amitchandak
Super User
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-trend/ba-p/882970
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p/881739
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601 - mwegener
Most Valuable Professional
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. - Ashish_Mathur
Hi,
Assuming that the null are actually blank cells, try these transformation steps in the Query Editor:
- Right click on the first heading and select Unpivot other columns
- Click on the Attribute column and go to Transform > Pivot Column. Select Value. Click on Advanced Options and select Don't Aggregate
- Click on OK
Hope this helps.
v-lionel-msftCommunity 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 ChenIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- mwegener
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.
