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
Hello Anonymous
check out this example if works for you
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,{{"Date", type date}, {"Store ID", type text}, {"SKU", type text}, {"Store Name", type text}, {"Notes", type text}}),
#"Added Custom" = Table.AddColumn
(
#"Changed Type",
"DateRange",
each Table.FromList
(
List.Dates
(
List.Min(#"Changed Type"[Date]),
Duration.TotalDays
(
List.Max(#"Changed Type"[Date])
-
List.Min(#"Changed Type"[Date])
)+1,
#duration(1,0,0,0)
),
Splitter.SplitByNothing(),
null,
null,
ExtraValues.Error
)
),
#"Grouped Rows" = Table.Group
(
#"Added Custom",
{"Store ID", "SKU"},
{{"AllRows", each _, type table [Date=date, Store ID=text, SKU=text, Store Name=text, Notes=text, DateRange=table]}}
),
Sort = Table.TransformColumns
(
#"Grouped Rows",
{{"AllRows",each Table.Sort(_, {{"Date", Order.Ascending}})}}
),
AddJoin = Table.AddColumn
(
Sort,
"JoinedTable",
each Table.Join
(
_[AllRows],
"Date",
_[AllRows][DateRange]{0},
"Column1",
JoinKind.FullOuter
)
),
#"Removed Other Columns" = Table.SelectColumns(AddJoin,{"JoinedTable"}),
#"Expanded JoinedTable" = Table.ExpandTableColumn(#"Removed Other Columns", "JoinedTable", {"Date", "Store ID", "SKU", "Store Name", "Notes", "DateRange", "Column1"}, {"Date", "Store ID", "SKU", "Store Name", "Notes", "DateRange", "Column1"}),
#"Filled Down" = Table.FillDown(#"Expanded JoinedTable",{"Store ID", "SKU", "Store Name", "Notes", "DateRange"}),
#"Removed Columns" = Table.RemoveColumns(#"Filled Down",{"Date", "DateRange"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Column1", "Date"}}),
#"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Date", "Store ID", "SKU", "Store Name", "Notes"})
in
#"Reordered Columns"
Copy paste this code to the advanced editor to see how the solution works
If this post helps or solves your problem, please mark it as solution.
Kudos are nice to - thanks
Have fun
Jimmy
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
- Hey
This comes from your data example that was converted in German. However, if you replace my dataset with yours it should work nicely
JimmyThank you both very much!!! It worked...
This has been a major headache for me and I am so happy that you have solved it!
Much appreciated.
Cheers,
Phil
Hello
you're welcome
appreciate a personal feedback
Jimmy
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