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

Merge a row with the previous one

Hello,

 

I am new to Power BI and I struggle merging some rows.

 

I have rows which have a column name in it and the actual data of those columns in the next line, like this:

 

table1.png

 

 

 

 

 

 

 

 

 

 

 

 

I want to replace those Title1 and Title2 by the actual data that's below. So far, I replaced the titles with null and renamed the columns, so it looks like that:

table2.png

 

 

 

 

 

 

 

 

 

 

 

Now I want to merge the data with a null key with the row above.

Any idea of how to do that in Power Query?

 

Thanks

1 ACCEPTED SOLUTION
MarkS
Resolver IV
Resolver IV

Hi @Anonymous,

Another way (if the null for key 2 and 3 are necessary to be kept) is to use some other string (or value) that is not in the dataset as a marker instead of null when replacing the TITLE1 and TITLE2 for example 'ZZZZZ'.

Then you can fill down on the UniqueKey column.

Then you can filter out the marker text from the Table1 and Table2 columns.

 

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

Thanks for the answers. I did a Fill down on the Key column and other columns I need.

 

@ImkeFMaybe my exemple was not exhaustive enough, but Fill up on the columns would not work in my case because Column 4 can be null sometimes, and in that case I don't want the data to be duplicated in the line above that has a different key.

 

MarkS
Resolver IV
Resolver IV

Hi @Anonymous,

Another way (if the null for key 2 and 3 are necessary to be kept) is to use some other string (or value) that is not in the dataset as a marker instead of null when replacing the TITLE1 and TITLE2 for example 'ZZZZZ'.

Then you can fill down on the UniqueKey column.

Then you can filter out the marker text from the Table1 and Table2 columns.

 

Greg_Deckler
Super User
Super User

@ImkeF may have some thoughts on this but the way to reference another row in Power Query goes something like this:

 

=if [Index] = 0 then fnSierpinskiInit("0,1") else fnSierpinskiInit(#"Renamed Columns"{[Index]-1}[Sierpinski])

Red highlighting of the important part. This is from my article here: https://www.linkedin.com/pulse/fun-graphing-power-bi-part-sqrtpowersqrt82-deckler-microsoft-mvp-/

 

 

 

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

In this case, "Fill up" will do: check your columns (except the KeyCol) -> right click your mouse -> Fill -> Up

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

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.