The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi All,
I am trying to convert 2 date type to an single date type but i am getting an issue .
I have a week column like below from which i split the column and extract the weekstart date .
I am getting correct output for everything other than for 4th Month.
Expression used for custom column:
try Date.FromText( [WeekStartDate], [Format = "MM/dd/yyyy" ] ) otherwise
Date.FromText( [WeekStartDate], [Format = "dd/MM/yyyy" ])
My week column has 2 different format.
MM/DD/YYYY | 01/01/2024 - 01/07/2024 |
MM/DD/YYYY | 01/08/2024 - 01/14/2024 |
MM/DD/YYYY | 01/15/2024 - 01/21/2024 |
MM/DD/YYYY | 01/22/2024 - 01/28/2024 |
MM/DD/YYYY | 01/29/2024 - 02/04/2024 |
MM/DD/YYYY | 02/05/2024 - 02/09/2024 |
MM/DD/YYYY | 02/12/2024 - 02/18/2024 |
MM/DD/YYYY | 02/19/2024 - 02/25/2024 |
MM/DD/YYYY | 02/26/2024 - 03/03/2024 |
DD/MM/YYYY | 04/03/2024 - 10/03/2024 |
DD/MM/YYYY | 11/03/2024 - 17/03/2024 |
DD/MM/YYYY | 18/03/2024 - 24/03/2024 |
DD/MM/YYYY | 25/03/2024 - 31/03/2024 |
DD/MM/YYYY | 01/04/2024 - 07/04/2024 |
DD/MM/YYYY | 08/04/2024 - 14/04/2024 |
DD/MM/YYYY | 15/04/2024 - 21/04/2024 |
DD/MM/YYYY | 22/04/2024 - 28/04/2024 |
DD/MM/YYYY | 29/04/2024 - 05/05/2024 |
Can someone please help me on this.
Thanks & Regards,
Poojashri
Solved! Go to Solution.
Hi,
Thanks for the solution @lbendlin provided and i want to offer some more information for user to refer to.
hello @poojashribanger based on your description, you can add a format column like the sample data you have offered "MM/DD/YYYY" or "DD/MM/YYYY", you can named it 'DateFormat' column, then based on this column you can add a index column first.
2.Add a custom column
=let a=Table.SelectRows(#"Added Index",(x)=>x[Index]=[Index]),
b=if [DateFormat]="DD/MM/YYYY" then Table.TransformColumnTypes(a, {{"WeekStartDate", type date}}, "en-GB") else Table.TransformColumnTypes(a, {{"WeekStartDate", type date}}, "en-US")
in b[WeekStartDate]{0}
Output
You can refer to the following m code in advanced editor in power query.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fdJNDoMgEAXgqxjWNsN7QsW9Ww/QGO9/jU7bgGPqQNiQ+ZL5YfY9bJusq7z0hDFESCzCyDQ8Bn0gfR/hGP8hsoGED0kLSwcuDVKim1pj2cLFh6CBcFNrzKZm9iGfDU6it0FVaitMNaYQ0YeAhXMHFgOZfKjFn3BCp0bUGX+amS8Dv8JioG6EC3UjTkj4kLSwdOBia8z142+3J8Ksmfbzg8cb", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DateFormat = _t, Column2 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"DateFormat", type text}, {"Column2", type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Column2", Splitter.SplitTextByEachDelimiter({"-"}, QuoteStyle.Csv, false), {"Column2.1", "Column2.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column2.1", type text}, {"Column2.2", type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Column2.1", "WeekStartDate"}}),
#"Replaced Value" = Table.ReplaceValue(#"Renamed Columns"," ","",Replacer.ReplaceText,{"WeekStartDate"}),
#"Added Index" = Table.AddIndexColumn(#"Replaced Value", "Index", 1, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each let a=Table.SelectRows(#"Added Index",(x)=>x[Index]=[Index]),
b=if [DateFormat]="DD/MM/YYYY" then Table.TransformColumnTypes(a, {{"WeekStartDate", type date}}, "en-GB") else Table.TransformColumnTypes(a, {{"WeekStartDate", type date}}, "en-US")
in b[WeekStartDate]{0})
in
#"Added Custom"
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
Thanks for the solution @lbendlin provided and i want to offer some more information for user to refer to.
hello @poojashribanger based on your description, you can add a format column like the sample data you have offered "MM/DD/YYYY" or "DD/MM/YYYY", you can named it 'DateFormat' column, then based on this column you can add a index column first.
2.Add a custom column
=let a=Table.SelectRows(#"Added Index",(x)=>x[Index]=[Index]),
b=if [DateFormat]="DD/MM/YYYY" then Table.TransformColumnTypes(a, {{"WeekStartDate", type date}}, "en-GB") else Table.TransformColumnTypes(a, {{"WeekStartDate", type date}}, "en-US")
in b[WeekStartDate]{0}
Output
You can refer to the following m code in advanced editor in power query.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fdJNDoMgEAXgqxjWNsN7QsW9Ww/QGO9/jU7bgGPqQNiQ+ZL5YfY9bJusq7z0hDFESCzCyDQ8Bn0gfR/hGP8hsoGED0kLSwcuDVKim1pj2cLFh6CBcFNrzKZm9iGfDU6it0FVaitMNaYQ0YeAhXMHFgOZfKjFn3BCp0bUGX+amS8Dv8JioG6EC3UjTkj4kLSwdOBia8z142+3J8Ksmfbzg8cb", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DateFormat = _t, Column2 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"DateFormat", type text}, {"Column2", type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Column2", Splitter.SplitTextByEachDelimiter({"-"}, QuoteStyle.Csv, false), {"Column2.1", "Column2.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column2.1", type text}, {"Column2.2", type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Column2.1", "WeekStartDate"}}),
#"Replaced Value" = Table.ReplaceValue(#"Renamed Columns"," ","",Replacer.ReplaceText,{"WeekStartDate"}),
#"Added Index" = Table.AddIndexColumn(#"Replaced Value", "Index", 1, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each let a=Table.SelectRows(#"Added Index",(x)=>x[Index]=[Index]),
b=if [DateFormat]="DD/MM/YYYY" then Table.TransformColumnTypes(a, {{"WeekStartDate", type date}}, "en-GB") else Table.TransformColumnTypes(a, {{"WeekStartDate", type date}}, "en-US")
in b[WeekStartDate]{0})
in
#"Added Custom"
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks ,
I tweaked a bit according to my requirement and it worked.
That is impossible as you cannot disambiguate between 05/06 and 06/05 etc. You need to have some other flag that can indicate which is which.
If you really have date ranges like in your example then you could monitor if the difference between the first two digits is 7 or not. If it is the your format is DD/MM, otherwise it is MM/DD. This will need to be tweaked for weeks stretching over two months. Instead of 7 you can do a cutoff at 2.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
39 | |
21 | |
19 | |
16 | |
13 |