Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hi All,
I trying to transform a set of dates like the one shown in the code to a list that starts on a spüecific date and moves on until today. I need this because I need to expand the records and automatically have the dates. Otherwise, the expansion is not considering new dates.
Table.ExpandRecordColumn( #"Converted to Table", "Value", {"2017-11-01", "2017-11-02", "2017-11-03", "2017-11-04", "2017-11-05", "2017-11-06", "2017-10-02", "2017-11-08", "2017-11-09", "2017-10-14", "2017-10-15", "2017-10-16", "2017-10-17", "2017-10-10", "2017-10-11", "2017-10-12", "2017-10-13", "2017-10-18", "2017-10-19", "2017-11-19", "2017-11-18", "2017-11-17", "2017-11-16", "2017-11-15", "2017-11-14", "2017-11-13"})
I tried to create a list like this:
{Number.From(Date.FromText("2017-11-01"))..Number.From(Date.FromText("2017-12-08"))}
But I need them to be Text values so I tried to convert the list to text, also doesn't work.
Does anyone have an idea how to do it? or if there any other way to get the data from the records directly into a table without the expand?
Best,
J.
Solved! Go to Solution.
You can try
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlSK1YlWSlKKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Record.FromTable(Table.FromRows({{"2017-11-01",1} , {"2017-11-02", 2}},{"Name", "Value"}) )), #"Converted to Table"= Table.RenameColumns(#"Added Custom",{{"Custom", "value"}}), list = List.Dates(#date(2017, 11, 1), Duration.Days(Duration.From(DateTime.Date(DateTime.LocalNow())-(#date(2017, 11, 1)))), #duration(1, 0, 0, 0)), ListTyped = List.Transform(list , each Date.ToText(_,"yyyy-MM-dd") ), lastResult = Table.ExpandRecordColumn(#"Converted to Table", "value",ListTyped ) in lastResult
You can try
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlSK1YlWSlKKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Record.FromTable(Table.FromRows({{"2017-11-01",1} , {"2017-11-02", 2}},{"Name", "Value"}) )), #"Converted to Table"= Table.RenameColumns(#"Added Custom",{{"Custom", "value"}}), list = List.Dates(#date(2017, 11, 1), Duration.Days(Duration.From(DateTime.Date(DateTime.LocalNow())-(#date(2017, 11, 1)))), #duration(1, 0, 0, 0)), ListTyped = List.Transform(list , each Date.ToText(_,"yyyy-MM-dd") ), lastResult = Table.ExpandRecordColumn(#"Converted to Table", "value",ListTyped ) in lastResult
Glad to help. 🙂
User | Count |
---|---|
136 | |
73 | |
73 | |
58 | |
54 |
User | Count |
---|---|
194 | |
95 | |
63 | |
63 | |
51 |