Forum Discussion
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 ...
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
- 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
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.
Mariusz
If this post helps, then please consider Accepting it as the solution.
Hi all,
can I add my version :)?
let
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]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Store ID", type text}, {"SKU", type text}, {"Store Name", type text}, {"Notes", type text}}),
#"Parsed Date" = Table.TransformColumns(#"Changed Type",{{"Date", each Date.From(DateTimeZone.From(_, "de-DE")), type date}}),
#"Grouped Rows" = Table.Group(#"Parsed Date", {"Date"}, {{"Data", each _, type number}}),
#"Sorted Rows" = Table.Buffer(Table.Sort(#"Grouped Rows",{{"Date", Order.Ascending}})),
//Setting the stage //
MinDate = List.Min(#"Sorted Rows"[Date]),
MaxDate = List.Max(#"Sorted Rows"[Date]),
DateRange = List.Generate(()=>MinDate, each _ <= MaxDate, each Date.AddDays(_,1)),
//Actual work
MakeTable = Table.FromRecords(List.Accumulate(DateRange, {}, (s,a)=> s & { Record.Combine({[Date = a], [Data = Table.Last(Table.SelectRows(#"Sorted Rows", each [Date]<=a))[Data]]})})),
#"Expanded Data" = Table.ExpandTableColumn(MakeTable, "Data", {"Date", "Store ID", "SKU", "Store Name", "Notes"}, {"Date.1", "Store ID", "SKU", "Store Name", "Notes"})
in
#"Expanded Data"
Kind regards,
JB
Anonymous
Hi JB,
Thanks for getting back to me, I really appreciate the assistance...
When I paste your code into my file it seems to work partially, however, the result is I now only have data from the 2nd of July to the 8th of July.
I was expecting to have data for the entire month (or from the 1st until the 26th)
Does that make sense?
Cheers,
Phil
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