Forum Discussion
Anonymous
6 years agoNot applicable
Query Editor M Code to fill as duplicate rows between dates
So my data in query editor is structured as such: What's the best way to add rows to this table that are duplicated between each date. For example, the 1st row shown should be duplicated an...
- 6 years ago
Hi Anonymous ,
Check this file as example: Download PBIX
Did I answer your question? Mark my post as a solution!
Ricardo
camargos88
6 years agoCommunity Champion
Hi Anonymous ,
Check this file as example: Download PBIX
Did I answer your question? Mark my post as a solution!
Ricardo
AlB
6 years agoCommunity Champion
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCslMzk4tMlTSUQooykxOBTEMDPWByMjAyEApVgemwgimwgiqwghdhTFMhTFUhTFURSwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Ticker = _t, Price = _t, DateColumn = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Ticker", type text}, {"Price", type text}, {"DateColumn", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Next_date", each List.Min(List.Select(Source[DateColumn], (inner)=> Date.From(inner) >[DateColumn]) ) ),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Next_date", type date}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type1", "dates_list", each if [Next_date]=null then {[DateColumn]} else List.Generate(()=>Date.AddDays(Date.From([DateColumn]),0), (inner)=> inner<Date.From([Next_date]),each Date.AddDays(_,1))),
#"Expanded dates_list" = Table.ExpandListColumn(#"Added Custom1", "dates_list"),
#"Removed Columns" = Table.RemoveColumns(#"Expanded dates_list",{"DateColumn", "Next_date"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"dates_list", "DateColumn"}})
in
#"Renamed Columns"
Please mark the question solved when done and consider giving kudos if posts are helpful.
Cheers