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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
MohsinBipu
Helper I
Helper I

How to delete all Nulls cells and move text contained cell into left side.

Hello,

I have imported a PDF file into power query. It looks like the below.

Null position is very random. I cannot delete Row or Column based on filter... 

MohsinBipu_1-1677587950919.png

 

I want to delete all null cells and need to move the text-contained cells into the left side. My expected outcome will be as below-

MohsinBipu_3-1677588445398.png

 

1 ACCEPTED SOLUTION
wdx223_Daniel
Super User
Super User

NewStep=Table.FormColumns(List.Zip(List.RemoveNulls(List.Transform(Table.ToRows(PreviousStepName),each if List.NonNullCount(_)=0 then null else List.RemoveNulls(_)))))

View solution in original post

7 REPLIES 7
MohsinBipu
Helper I
Helper I

@wdx223_Daniel As a Consequence of this query, I am posting a new topic to identify a text format (for my case XXX-XX-XX, example 122-22-10). and then take that cells including the upper cells. If you can help, please help to advise on the new topic.

Link: How to identify a text format in power query - Microsoft Power BI Community

MohsinBipu_0-1677729975014.png

 

wdx223_Daniel
Super User
Super User

NewStep=Table.FormColumns(List.Zip(List.RemoveNulls(List.Transform(Table.ToRows(PreviousStepName),each if List.NonNullCount(_)=0 then null else List.RemoveNulls(_)))))

@wdx223_Daniel 

Tried to follow your formula. My bad luck is that this is not giving me results. Please help me how to apply?

MohsinBipu_0-1677694000572.png

 

 

@wdx223_Daniel Thanks. I sort it out. Thanks like magic. you are the real super user of PBI 🙂

= Table.FromColumns (List.Zip(List.RemoveNulls(List.Transform(Table.ToRows(#"Appended Query"),each if List.NonNullCount(_)=0 then null else List.RemoveNulls(_)))))

MohsinBipu_1-1677730633089.png

 

adudani
Super User
Super User

hi @MohsinBipu ,

 

reference :

The first link maps headers and checks if the column name contains "Column" after promotnig headers for nulls.

 (25) Dynamically Remove Empty Columns in Power Query - YouTube

 

This one uses unpivot to remove nulls. might not be the best solution for this

(25) DYNAMICALLY Remove All Empty Columns with some M MAGIC in Power BI - YouTube

 

let me know if this resolves with your use case. if not, could you please provide sample input file removing sensitive information?

Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to drop me a Kudos 🙂
Kind Regards,
Avinash

@adudani Thanks for the below link. I watched and learn new things. But this is solving my problem as my NULL are very random. Thanks. 

alannavarro
Resolver I
Resolver I

Maybe there is another easy way to do this,
What I did is quite confusing:
1.- Select all columns and replace null for a special character that is not going to be in the data for example !
2.- Select all columns and merge all columns with the same special character as delimiter !
3.- Now you are going to have only 1 column. Select that column and replace values !! with nothing (an empty value)
4.- Select the column and split by delimiter !


Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors