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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
lotus22
Helper III
Helper III

Bulk "Replace" Multiple columns in Power Query that has date older than certain value

I would like to bulk replace multiple columns in PowerQuery with dates older 1/1/2000 to Blank

 

Is this possible?

 

Date1Date2Date3Date4
9/6/19935/6/19934/1/20258/1/2019
4/1/20194/1/20191/1/19914/6/1992
5/23/19925/23/20231/1/20115/4/1994
8/1/20228/1/20194/1/20199/1/2019
1 ACCEPTED SOLUTION
DataZoe
Microsoft Employee
Microsoft Employee

Edit: I realized I could do it in one step

 

@lotus22 You could try this in your PowerQuery:

 

let

fChange = (x as date) =>
let
result = if x <= #date(2000,1,1) then null else x
in
result,

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VY5BDoAwCAT/wpkEodTI3V80PXjwB/4/Ulq1nnbYLCylwH5cJwOGytA0VKFiAaOV2KyZ+UMlJlkkO26BbJHWZ8AZ2dEXOdy4IZHOJKlP2NlPppH3VQ5bW6L/0qtE5tZfkb2/1Bs=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}, {"(blank).3", type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date1", type date}, {"Date2", type date}, {"Date3", type date}, {"Date4", type date}}),
Transform = Table.TransformColumns(#"Changed Type1",{{"Date1", each fChange( _ )},{"Date2", each fChange( _ )},{"Date3", each fChange( _ )},{"Date4", each fChange( _ )}})
in
Transform

 

DataZoe_0-1611616107339.png

Respectfully,
Zoe Douglas (DataZoe)



Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/

View solution in original post

2 REPLIES 2
v-yiruan-msft
Community Support
Community Support

Hi @lotus22 ,

You can achieve it by Unpivot column and adding a custom column in Power Query Editor besides the method as suggested by @DataZoe , you can copy and paste the below applied codes in your Advanced Editor:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VY7LDcAwCEN34YxE+VVlFpT91yglUZveni0bkwlBJ3GEAoJ/aMQkh3jh1cgBA3P5JXBHLqwit9s3pNNOolPh5DqpK19VbtuehHV+Tonsq7+heH8ZNw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date1 = _t, Date2 = _t, Date3 = _t, Date4 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date1", type date}, {"Date2", type date}, {"Date3", type date}, {"Date4", type date}}),
    #"Unpivoted Only Selected Columns" = Table.Unpivot(#"Changed Type", {"Date1", "Date2", "Date3", "Date4"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Only Selected Columns", "Custom", each if [Value]<#date(2000,1,1) then null else [Value]),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Value"}),
    #"Added Index" = Table.AddIndexColumn(#"Removed Columns1", "Index", 0, 1, Int64.Type),
    #"Integer-Divided Column" = Table.TransformColumns(#"Added Index", {{"Index", each Number.IntegerDivide(_, 4), Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Integer-Divided Column", List.Distinct(#"Integer-Divided Column"[Attribute]), "Attribute", "Custom"),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index"})
in
    #"Removed Columns"

 

bulk replace the values in multiple columns.JPG

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
DataZoe
Microsoft Employee
Microsoft Employee

Edit: I realized I could do it in one step

 

@lotus22 You could try this in your PowerQuery:

 

let

fChange = (x as date) =>
let
result = if x <= #date(2000,1,1) then null else x
in
result,

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VY5BDoAwCAT/wpkEodTI3V80PXjwB/4/Ulq1nnbYLCylwH5cJwOGytA0VKFiAaOV2KyZ+UMlJlkkO26BbJHWZ8AZ2dEXOdy4IZHOJKlP2NlPppH3VQ5bW6L/0qtE5tZfkb2/1Bs=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}, {"(blank).3", type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date1", type date}, {"Date2", type date}, {"Date3", type date}, {"Date4", type date}}),
Transform = Table.TransformColumns(#"Changed Type1",{{"Date1", each fChange( _ )},{"Date2", each fChange( _ )},{"Date3", each fChange( _ )},{"Date4", each fChange( _ )}})
in
Transform

 

DataZoe_0-1611616107339.png

Respectfully,
Zoe Douglas (DataZoe)



Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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