Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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:

 
Col1Col2Col3Col4Col5
Col1ValueCol2Valuenullnullnull
Col1ValuenullCol3Valuenullnull
Col1ValuenullnullCol4Valuenull
Col1ValuenullnullnullCol5Value

 

(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

 

Col1Col2Col3Col4Col5
Col1ValueCol2ValueCol3ValueCol4ValueCol5Value

 

Is this possible to do this via Power Query transformations/DAX?

 

Let me know if this is unclear

 

Thanks in advance for your help

1 ACCEPTED SOLUTION

Hi @Anonymous ,

 

you can use Power Query with Group By

 

PQGroupBy.png

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.

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


View solution in original post

5 REPLIES 5
v-lionel-msft
Community Support
Community 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])
)

x4.PNG

 

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.

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


Ashish_Mathur
Super User
Super User

Hi,

Assuming that the null are actually blank cells, try these transformation steps in the Query Editor:

  1. Right click on the first heading and select Unpivot other columns
  2. Click on the Attribute column and go to Transform > Pivot Column.  Select Value.  Click on Advanced Options and select Don't Aggregate
  3. Click on OK

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi @Anonymous ,

 

you can use Power Query with Group By

 

PQGroupBy.png

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.

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


amitchandak
Super User
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-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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.