Data manipulation Help - Date Range

[PowerRobots99]

Input Table :--

Transformed Output Table:-

Please let me know is there any way to achieve this..

[_AAndrade · Accepted solution]

Sure. I'm attaching the pbix file.

[Ashish_Mathur · Accepted solution]

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.

[v-kaiyue-msft · Accepted solution]

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.

[Ashish_Mathur · Accepted solution]

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.

[_AAndrade]

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.

[PowerRobots99]

Could you please attach .pbix file here, it will help in understanding..

[_AAndrade · Accepted solution]

Sure. I'm attaching the pbix file.