Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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...
Need Help
  • camargos88's avatar
    camargos88
    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's avatar
camargos88
Community Champion
6 years ago

Hi Anonymous ,

 

Can you explain what are you trying to achieve ? I didn't get the logic for it.

 

Ricardo

  • AlB's avatar
    AlB
    Community Champion
    6 years ago

    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?

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    camargos88

    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.

    • camargos88's avatar
      camargos88
      Community Champion
      6 years ago

      Hi  Anonymous ,

       

       

      Check this file as example: Download PBIX 

       

      Did I answer your question? Mark my post as a solution!
      Ricardo

      • AlB's avatar
        AlB
        Community Champion
        6 years ago
        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