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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Mohan128256
Helper II
Helper II

Replace multiple DATE columns values with null based on a condition

Hello All,

 

I have data as below where i am trying to repalce multiple(almost 40 columns) date columns values with nulls where the dates are 1900-01-01 and 2000-01-01 else it should return the dates.

Mohan128256_0-1683905720026.png

I have tried the below power query but not successful.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTK0NDDQNTAEIiAnEIgNlGJ1opWcgCwjAySpcJBasJQzWMoQKGWsa2QM5LjCpVzAUkbGuoa6qMY5QiSAZpnoGpiiGIdhkytEVywA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type date}, {"Column3", type text}, {"Column4", Int64.Type}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Column2", "Column5"),
    Custom3 = List.Accumulate( {"Column2","Column5"}, #"Duplicated Column", (s,a)=> Table.ReplaceValue( s, each Record.Field( _, a), each if Text.From( Record.Field( _, a)) = "1900-01-01" then null else if Text.From( Record.Field( _, a)) = "2000-01-01" then null else _, Replacer.ReplaceValue, {a} ))
in
    Custom3

 

It returning as below which is not right.

Mohan128256_1-1683905867543.png

Anyone please correct what i am doing wrong here.

Thanks,

Mohan V.

 

1 ACCEPTED SOLUTION
ThxAlot
Super User
Super User

Simple enough, no need to specify any column name.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY4xCoAwDEXvkrmBpNXBsWoP4ORQev9r+FMhtiAkw3888lMrZQqkmwiLYhAurMywhUo7UJTBu82ZoXlHRwqUOCaE4p5D886OYmLlsdWROfkFuL2wrFOnw9/fil/7fmsP", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type date}, {"Column3", type text}, {"Column4", Int64.Type}, {"Column5", type date}}),

    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",null,null,(x,y,z) => if List.Contains({#date(1900,1,1),#date(2000,1,1)},x) then null else x,Table.ColumnNames(Source))
in
    #"Replaced Value"

ThxAlot_0-1683940063931.png



Expertise = List.Accumulate(


        {Days as from Today},


        {Skills and Knowledge},


        (Current, Everyday) => Current & Day.LeanAndPractise(Everyday)


)



View solution in original post

4 REPLIES 4
ThxAlot
Super User
Super User

Simple enough, no need to specify any column name.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY4xCoAwDEXvkrmBpNXBsWoP4ORQev9r+FMhtiAkw3888lMrZQqkmwiLYhAurMywhUo7UJTBu82ZoXlHRwqUOCaE4p5D886OYmLlsdWROfkFuL2wrFOnw9/fil/7fmsP", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type date}, {"Column3", type text}, {"Column4", Int64.Type}, {"Column5", type date}}),

    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",null,null,(x,y,z) => if List.Contains({#date(1900,1,1),#date(2000,1,1)},x) then null else x,Table.ColumnNames(Source))
in
    #"Replaced Value"

ThxAlot_0-1683940063931.png



Expertise = List.Accumulate(


        {Days as from Today},


        {Skills and Knowledge},


        (Current, Everyday) => Current & Day.LeanAndPractise(Everyday)


)



m_dekorte
Super User
Super User

Hi @Mohan128256 

 

Give this a go.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTK0NDDQNTAEIiAnEIgNlGJ1opWcgCwjAySpcJBasJQzWMoQKGWsa2QM5LjCpVzAUkbGuoa6qMY5QiSAZpnoGpiiGIdhkytEVywA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type date}, {"Column3", type text}, {"Column4", Int64.Type}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Column2", "Column5"),
    Custom3 = List.Accumulate( {"Column2","Column5"}, #"Duplicated Column", (s,a)=> Table.ReplaceValue( s, each Record.Field( _, a), each if Text.From( Record.Field( _, a)) = "1-1-1900" then null else if Text.From( Record.Field( _, a)) = "1-1-2000" then null else Record.Field( _, a), Replacer.ReplaceValue, {a} ))
in
    Custom3

 

with this result

m_dekorte_0-1683916435957.png

 

Ps. If this helps solve your query please mark this post as Solution, thanks!

@m_dekorte  thanks for the reply. I really appreciate your time.

I tried what you have given but i dont see any changes from my end.

Mohan128256_0-1683919283025.png

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTK0NDDQNTAEIiAnEIgNlGJ1opWcgCwjAySpcJBasJQzWMoQKGWsa2QM5LjCpVzAUkbGuoa6qMY5QiSAZpnoGpiiGIdhkytEVywA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type date}, {"Column3", type text}, {"Column4", Int64.Type}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Column2", "Column5"),
    Custom3 = List.Accumulate( {"Column2","Column5"}, #"Duplicated Column", (s,a)=> Table.ReplaceValue( s, each Record.Field( _, a), each if Text.From( Record.Field( _, a)) = "1-1-1900" then null else if Text.From( Record.Field( _, a)) = "1-1-2000" then null else Record.Field( _, a), Replacer.ReplaceValue, {a} ))
in
    Custom3

Am i Missing anything here.

 

Please correct.

Hi @Mohan128256,

 

Works on my end, most likely a system- or regional setting... try this instead.

 

List.Accumulate( {"Column2","Column5"}, #"Duplicated Column",
    (s,a)=> Table.ReplaceValue( s, each Record.Field( _, a),
        each if Date.From( Record.Field( _, a)) = #date(1900, 1, 1) then null
        else if Date.From( Record.Field( _, a)) = #date(2000, 1, 1) then null
        else Date.From( Record.Field( _, a)), Replacer.ReplaceValue, {a} ))

 

Ps. If this helps solve your query please mark this post as Solution, thanks!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors