Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Input Table :--
Transformed Output Table:-
Please let me know is there any way to achieve this..
Solved! Go to Solution.
Sure. I'm attaching the pbix file.
Proud to be a Super User!
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.
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.
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.
Proud to be a Super User!
Could you please attach .pbix file here, it will help in understanding..
User | Count |
---|---|
93 | |
86 | |
78 | |
72 | |
66 |
User | Count |
---|---|
115 | |
106 | |
85 | |
65 | |
64 |