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
Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi Jimmy,

 

Thank you for getting back to me, I sincerely appreciate it!

 

When I paste your code into my file I get the following error "Mittwoch, 3. Juli 2019"

 

Do you know why that is?

 

Cheers,

 

Phil

 

 

 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

This is a Geman dates format, can be resolved by replacing the following code:

 

#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Store ID", type text}, {"SKU", type text}, {"Store Name", type text}, {"Notes", type text}}),

 

With this:

#"Parsed Date" = Table.TransformColumns(Source,{{"Date", each Date.From(DateTimeZone.From(_, "de-DE")), type date}}),
#"Changed Type" = Table.TransformColumnTypes(#"Parsed Date",{{"Store ID", type text}, {"SKU", type text}, {"Store Name", type text}, {"Notes", type text}}),

Kind regards,

JB