Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon'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.
I would like to bulk replace multiple columns in PowerQuery with dates older 1/1/2000 to Blank
Is this possible?
Date1 | Date2 | Date3 | Date4 |
9/6/1993 | 5/6/1993 | 4/1/2025 | 8/1/2019 |
4/1/2019 | 4/1/2019 | 1/1/1991 | 4/6/1992 |
5/23/1992 | 5/23/2023 | 1/1/2011 | 5/4/1994 |
8/1/2022 | 8/1/2019 | 4/1/2019 | 9/1/2019 |
Solved! Go to Solution.
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
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/
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"
Best Regards
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
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/
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
144 | |
80 | |
65 | |
52 | |
49 |
User | Count |
---|---|
212 | |
89 | |
80 | |
69 | |
60 |