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
Anonymous
Not applicable

Auto replace all values in all columns with specific value

Hello,

I have the following table loaded in Power Query which is updated regularly with changing number of columns in the source Excel spreadsheet. I am trying to find a dynamic way to replace all values in the entire table where it is 0 to null. Is this possible?

 

Currently, this is the step used but it is not dynamic when the columns change:

 

= Table.ReplaceValue(Source,"0",null,Replacer.ReplaceValue,{"Week number", "Stock", "Sold", "Returns"})

 

 

Week numberStockSoldReturns
11218932
221220012
314200
48468451
5050052
62310200
7514510
8874551323
9084464
10152540
115600360
126146140
1 ACCEPTED SOLUTION
artemus
Microsoft Employee
Microsoft Employee

You can replace 

{"Week number", "Stock", "Sold", "Returns"}

with

Table.ColumnNames(Source)

View solution in original post

1 REPLY 1
artemus
Microsoft Employee
Microsoft Employee

You can replace 

{"Week number", "Stock", "Sold", "Returns"}

with

Table.ColumnNames(Source)

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors