Forum Discussion

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

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
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

Jimmy801

Community Champion

6 years ago

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

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

Not applicable

6 years ago

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,

Anonymous

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

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

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
- Jimmy801
  Community Champion
  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
    Not applicable
    6 years ago
    Thank 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
- 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

Forum Discussion

Copy data from previous day when date is missing

Related content

Fabric copy activity fails when JSON source is missing attributes

missing data

Generating Missing Data

missing data

Inferring Missing Columns and Imputing Missing Data