Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi!
is it powible to transform a table whith with data-range:
To this (a date range for every month into initial data-range):
Regards!
Solved! Go to Solution.
I get the solution:
First i create a column witht init date:
List.Distinct( List.Transform(
List.Dates([INIT],Number.From([END]-[INIT])+1,#duration(1,0,0,0)),each Date.StartOfMonth(_)))
Second I create the list as rows.
Last I create enddatecolumn:
Date.EndOfMonth([INITMONTH])
I get the solution:
First i create a column witht init date:
List.Distinct( List.Transform(
List.Dates([INIT],Number.From([END]-[INIT])+1,#duration(1,0,0,0)),each Date.StartOfMonth(_)))
Second I create the list as rows.
Last I create enddatecolumn:
Date.EndOfMonth([INITMONTH])
Power Query code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIw1AciIwMjEMcYyDGGcGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, init = _t, end = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"id", Int64.Type}, {"init", type date}, {"end", type date}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type", "Custom.1", each {Number.From([init])..Number.From([end])}),
#"Expanded Custom.1" = Table.ExpandListColumn(#"Added Custom1", "Custom.1"),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom.1",{{"Custom.1", type date}}),
#"Inserted Start of Month" = Table.AddColumn(#"Changed Type1", "Start of Month", each Date.StartOfMonth([Custom.1]), type date),
#"Inserted End of Month" = Table.AddColumn(#"Inserted Start of Month", "End of Month", each Date.EndOfMonth([Custom.1]), type date),
#"Removed Columns" = Table.RemoveColumns(#"Inserted End of Month",{ "init", "end", "Custom.1"}),
#"Removed Duplicates" = Table.Distinct(#"Removed Columns")
in
#"Removed Duplicates"
There are quite a few steps but 90% of it is done using the interface.
Hi @Anonymous ,
I don't think you can do this in Power BI. You will have to bring in a complete file with the filled values in the data.
Thanks,
Pragati
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |