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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Buckley
New Member

Change all dates in column to null

Hi all, hope you can help!

 

I need to remove all dates within the below column. The idea is, that if I can remove the dates I can then pivot the column out to produce new columns for each text string. 

 

I tired to replace values using the wild card **/**/*** & ??/??/???? but with no effect. I've also tried this while the data type has been switched to "date". Also with no effect?

 

Any help you could give would be appreciated!

 

 

Buckley_1-1708961561670.png

 

 

2 ACCEPTED SOLUTIONS
Vijay_A_Verma
Super User
Super User

Use this as your next step to replace date with null

Table.ReplaceValue(Source, each [WFM Activity], each if (Value.Is([a = Text.Split([WFM Activity],"/"), d = #date(Number.From(a{2}), Number.From(a{1}), Number.From(a{0}))][d], type date)) then null else [WFM Activity], Replacer.ReplaceValue, {"WFM Activity"})

 

View solution in original post

dufoq3
Super User
Super User

Hi @Buckley, what about this?

dufoq3_0-1709217319837.png

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUorVgRIGxvoGhvpGBkYmYG5IamKuglNRZmoaRNYERdYltSw1J78gNzWvRCEkMzcVLOibmlqSmZcOZhuaItTHAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"WFM Activity" = _t]),
    Replace = Table.ReplaceValue(Source,
     null,
     null,
     (x,y,z)=> if (try Date.From(x, "sk-SK") otherwise x) is date or Text.Trim(x) = "" then null else x, //you can delete    or Text.Trim(x) = ""   if you want to replace only dates
     {"WFM Activity"} )
in
    Replace

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

View solution in original post

2 REPLIES 2
dufoq3
Super User
Super User

Hi @Buckley, what about this?

dufoq3_0-1709217319837.png

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUorVgRIGxvoGhvpGBkYmYG5IamKuglNRZmoaRNYERdYltSw1J78gNzWvRCEkMzcVLOibmlqSmZcOZhuaItTHAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"WFM Activity" = _t]),
    Replace = Table.ReplaceValue(Source,
     null,
     null,
     (x,y,z)=> if (try Date.From(x, "sk-SK") otherwise x) is date or Text.Trim(x) = "" then null else x, //you can delete    or Text.Trim(x) = ""   if you want to replace only dates
     {"WFM Activity"} )
in
    Replace

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Vijay_A_Verma
Super User
Super User

Use this as your next step to replace date with null

Table.ReplaceValue(Source, each [WFM Activity], each if (Value.Is([a = Text.Split([WFM Activity],"/"), d = #date(Number.From(a{2}), Number.From(a{1}), Number.From(a{0}))][d], type date)) then null else [WFM Activity], Replacer.ReplaceValue, {"WFM Activity"})

 

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors