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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

Top Solution Authors
Top Kudoed Authors