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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

Top Solution Authors
Top Kudoed Authors