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.
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 and have the same row for the dates 1/1/2021 through 1/31/2021. Then similar logic for the second row, it would be duplicated for the dates 2/1/2021 through 2/28/2021.
Solved! Go to Solution.
Hi @Anonymous ,
Check this file as example: Download PBIX
Did I answer your question? Mark my post as a solution!
Ricardo
The below is an example of the result for the first row.
This would be replicated for each row of the original table with the additional rows being the dates between.
Hi @Anonymous ,
Check this file as example: Download PBIX
Did I answer your question? Mark my post as a solution!
Ricardo
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
Hi @Anonymous
Can you provide sample data in tex-tabular format rather than in a screen capture? So that it can be copied and used for a quick test?
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 |
---|---|
114 | |
99 | |
83 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |