Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |