Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Copy data from previous day when date is missing

Hi there clever people,   I have a list of products that should be in stock in each of our retail stores on a particular date.  This product range file is however not created every day, so there a ...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Sorry, I was using the source from Jimmy's sample.

     

    Please replace 

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tde9bsIwEADgV7EyR1UIpYWxiKF/IAqoHRCDGwyxcBzkOEXp09d06NBDPp8lkJDCwKfc+WzfrdfJVFp7qosyZf0b9twqyfKsN0rSZCEslyrLcvecD4Z5dv7sGvdrzLsdl8Y9PWm24Hovkk0aIPWB9CGUknpva03FehnAlpa9tgfRkKkepDr1JTmbc3MgazDKVSnYmDeCPfJKqphgby+ac8W/zy+pKmEax87I7h1w32Vha+NiX1ojhCWvMczlg/rkuiNDMORJq8VW0pM3BNJCFgU3EQuRw6qbyi17a4XQsRkbAHLGj1LQtxdczJlQDT3I7P4CdHLV1lV1a0syB/PvuMptrYhkjYDl9ig/um9bteTKgNpfZbAXWTf0zQ9T91G6f3MjJNmChbGSLm30MOEJvuKtOb/Wf2oihW4s36csvzoVGh4KEXKOWuSKwERaxaIaYS+hFm2bo1zo8YNCoQciBkUc1mh9EO4SNM7gGw6VQi9dNDxyP4CKkZ0L6kZ0WahJ6gNRLbxBRdeX0jijWHg/X+tfZ3hdhzQVeCnKTOCHaBOB34qZB/xi7DTgV+mzgH9dgycBPxM+B/jDo0wBXilmBvCHGDwB+Jng/t/PELt/P0bp/f0SrfP31wK97/d7oR3o5gc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, #"Store ID" = _t, SKU = _t, #"Store Name" = _t, Notes = _t]),

     

    With something like this (from Mariusz's code):

    Source = #"Test Data xlsx",

     

    This is essentially the link to your data loaded from Excel.

     

    Kind regards,

    John

     

     

  • Jimmy801's avatar
    Jimmy801
    6 years ago
    Hey
    This comes from your data example that was converted in German. However, if you replace my dataset with yours it should work nicely
    Jimmy
Mariusz's avatar
Mariusz
Community Champion
6 years ago

Hi Anonymous 

 

Please see the below script, you can simply add it to your Power Query file.

It will reference "Test Data xlsx" table and do all the necessary transformation.

 

let
    Source = #"Test Data xlsx",
    #"Grouped Rows" = Table.Group(Source, {"Date"}, {{"tbl", each _, type table }}),
    #"min date" = Number.From( List.Min( #"Grouped Rows"[Date] ) ),
    #"max date" = Number.From( List.Max( #"Grouped Rows"[Date] ) ),
    #"list all days" = { #"min date" .. #"max date" },
    #"Converted to Table" = Table.FromList(#"list all days", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Date"}, #"Grouped Rows", {"Date"}, "Changed Type", JoinKind.LeftOuter),
    #"Expanded Changed Type" = Table.ExpandTableColumn(#"Merged Queries", "Changed Type", {"tbl"}, {"tbl"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Changed Type",{{"Date", Order.Ascending}}),
    #"Filled Down" = Table.FillDown(#"Sorted Rows",{"tbl"})
in
    #"Filled Down"

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi Mariusz,

 

Thank you for getting back to me, much appreciated!

 

Unfortunately, when I try your code I get the following error "Expression.Error: A cyclic reference was encountered during evaluation."

 

I simply pasted the code into the Advanced Editor, am I doing something wrong?

 

Cheers,

 

Phil