Forum Discussion
Data manipulation Help - Date Range
Input Table :--
Transformed Output Table:-
Please let me know is there any way to achieve this..
Sure. I'm attaching the pbix file.
Hi,
This M code works
let Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start", type date}, {"End", type date}, {"Job", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Date", each {Number.From([Start])..Number.From([End])}), #"Expanded Date" = Table.ExpandListColumn(#"Added Custom", "Date"), #"Removed Columns" = Table.RemoveColumns(#"Expanded Date",{"Start", "End"}), #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Date", type date}}) in #"Changed Type1"Hope this helps.
Hi PowerRobots99 ,
Your solution is great, _AAndrade , Ashish_Mathur . it works like a charm! Here, I have another idea and I would like to share it for reference.
You can create a calculated table that writes dax expressions:New Table = GENERATE( 'Table'. VAR _start_date = 'Table'[start] VAR _end_date = 'Table'[end] RETURN FILTER( CALENDAR(_start_date,_end_date), [Date] >= _start_date && [Date] <= _end_date))If your Current Period does not refer to this, please clarify in a follow-up reply.
Best Regards,
Clara Gong
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
Hi,
This M code works
let Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start", type date}, {"End", type date}, {"Job", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Date", each {Number.From([Start])..Number.From([End])}), #"Expanded Date" = Table.ExpandListColumn(#"Added Custom", "Date"), #"Removed Columns" = Table.RemoveColumns(#"Expanded Date",{"Start", "End"}), #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Date", type date}}) in #"Changed Type1"Hope this helps.
Hi PowerRobots99,
Here is my solution:let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUByIjAyMTJR0lAzMkjqNSrA5Q3hxJyNAIieMEljc0Rpa3QOI4Q+QtkYSMTJA4LkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Start = _t, End = _t, Unit = _t]), ChangedType = Table.TransformColumnTypes(Source,{{"Start", type date}, {"End", type date}, {"Unit", type text}}), Expand = Table.ExpandListColumn(Table.AddColumn(ChangedType, "Date", each List.Dates([Start], Duration.Days([End] - [Start]) + 1, #duration(1, 0, 0, 0))), "Date"), Result = Table.RemoveColumns(Expand, {"Start", "End"}) in Result
I hope this can solve your problem.Could you please attach .pbix file here, it will help in understanding..
Sure. I'm attaching the pbix file.
Hi PowerRobots99 ,
Your solution is great, _AAndrade , Ashish_Mathur . it works like a charm! Here, I have another idea and I would like to share it for reference.
You can create a calculated table that writes dax expressions:New Table = GENERATE( 'Table'. VAR _start_date = 'Table'[start] VAR _end_date = 'Table'[end] RETURN FILTER( CALENDAR(_start_date,_end_date), [Date] >= _start_date && [Date] <= _end_date))If your Current Period does not refer to this, please clarify in a follow-up reply.
Best Regards,
Clara Gong
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
