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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
pkee
Regular Visitor

Replace multiple values in a row of multiple columns in Power Query

Hi There,

I have an issue to replace values on different columns in a single row based on one condition.
Here, I have continous [date] column and [historic date] column. If date <> histroic date then i want to replace values(to null) of other columns in same row.
Here screenshot of query, Power query advance editorPower query advance editorErrorError

looking for help @amitchandak @Rickmaurinus 
Thanks
PK

1 ACCEPTED SOLUTION
spinfuzer
Solution Sage
Solution Sage

 

1) Change your second each argument to each [Historic Date] 

2) Change Replacer.ReplaceValue with (orig, dateVal, histVal) => if dateVal <> histVal then orig else null

3) This will mess up your column types so change them back to normal.

 

spinfuzer_3-1701213024424.png

 

 

 

 

 

 

View solution in original post

4 REPLIES 4
AlienSx
Super User
Super User

Hello, @pkee 

    other_columns = List.RemoveItems(Table.ColumnNames(your_table), {"Date", "Historic Date"}),
    blanks = Record.FromList(List.Repeat({null}, List.Count(other_columns)), other_columns),
    replacer = Table.FromRecords(
        Table.TransformRows(
            your_table,
            (x) => if x[Date] <> x[Historic Date] then Record.SelectFields(x, {"Date", "Historic Date"}) & blanks else x
        )
    )
spinfuzer
Solution Sage
Solution Sage

 

1) Change your second each argument to each [Historic Date] 

2) Change Replacer.ReplaceValue with (orig, dateVal, histVal) => if dateVal <> histVal then orig else null

3) This will mess up your column types so change them back to normal.

 

spinfuzer_3-1701213024424.png

 

 

 

 

 

 

Hello spinfuzer,

pkee_0-1701274684600.png

 


It's not working for me. Did i do something wrong?


Thanks
PK

I am not sure here, can you send a small data sample?  It is working with examples I am using.

Do you get test for every thing if you do something like 

(orig, dateVal, histVal) => "test" instead?

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

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