Filtering Report tabels with date requirement/ data cleaning

[DB_CJC]

Dear all,

1st resquest for support:

ich would like to display on my dashboard a table which is listing future changes. The table content was been created by drag and drop operations and calculations.

Currently the table is listing all available information, i would like to filter in the following manner:

(a) only rows shall be listed which have an entry in colum "target availability date"

(b) only rows shall be listed where the target availability date is higher than the beginning of last month

2nd request for support:

unfortunalty the entries in colum "target availability date" are not entered in the same structure. entries can look as follows:

(a) 01.01.2023

(b) 01/23

(c) tbc (2023)

(d) cw37/23

(e) 01/2023

(f) 01.10.2022 (tbc)

In order to have a good filtering result for request #1 i thought it might be helpful to transfer all availble date types into one common date format (ideally 01.01.2023). Do you have an idea by which function this might be avieved?

I would like to achive the entries may look als follows:

(a) 01.01.2023        --> leave as it is

(b) 01/23                --> transfer to 01.01.2023

(c) tbc (2023)          --> transfer to 01.01.2023

(d) cw37/23            --> trasnfer to 11.09.2023

(e) 02/2023             --> transfer to 01.02.2023

(f) 01.10.2023 (tbc) --> transfer to 01.10.2023

The data table looks as follows:

Part Name	IAL part number	internal part number	target availability date	PPAP stats
part A	123456	556633-01	01.02.2023	G
part B	654321	445533-02	37/23	Y
part C	145236	225566-05	tbc	G
part D	365214	223344-01	02/2023	R

Thanks alot for the support.

@v-luwang-msfti will continue the support request by this post.

With the following link i'll provide a sample file

https://www.dropbox.com/s/3d7xuxvsint4rpl/Sample_MSforum.pbix?dl=0

Thanks and best regards

[lbendlin · Accepted solution]

Ideally this unclean data would not even make it into Power BI.  Anyway, I would recommend against attempting to do this in DAX. Here is a framework of how to do it in Power Query.  I'll leave the calendar week gymnastics up to you

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUMzDSMzIwMlKK1YFwjVG5JqhcC1SuJQrX0AAbV0GjJClZEyxoaKAPVwBlK2iEODlDZQ2RZI2Q2BZ6BqYIk5PLjc31oWygyQoaQBljiAlGpgiVsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Target Availability Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Target Availability Date", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each 
try Date.From([Target Availability Date],"de-de")
otherwise try Date.From(Text.Start([Target Availability Date],10),"de-de")
otherwise try Date.From(Text.Start([Target Availability Date],8),"de-de")
otherwise try Date.From(Text.Range([Target Availability Date],5,4),"de-de")
otherwise null
,type date)
in
    #"Added Custom"

Please note that Power Query is case sensitive.