Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Dynamic Week Appending

Hello all, I'm stuck at some historical data problem. I will try to explane it, if you have any questions I can show you more of my problem.   For example, Company sends me data monthly.Data Contai...
Need Help
Show and Tell
Tips and Tricks
  • v-yingjl's avatar
    v-yingjl
    5 years ago

    Hi Anonymous ,

    Based on your description, you can use Merge and Append features in power query to achieve it easily.

    Merge Jan table and Feb table as a new table, expand the table column, filter rows and remove columns, the whole query is like this:

    let
    Source = Table.NestedJoin(#"Weekly February", {"date"}, #"Weekly January", {"date"}, "Weekly January", JoinKind.LeftOuter),
    #"Expanded Weekly January" = Table.ExpandTableColumn(Source, "Weekly January", {"date"}, {"Weekly January.date"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Weekly January", each ([Weekly January.date] = null)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Weekly January.date"})
in
    #"Removed Columns"

    You will get this table:

    Then append this table with Jan table as a new table:

    You can only load the result table and disable loading other tables to improve performance, close and apply it in power query:

    Attached a sample file in the below, hopes to help you.

     

    Best Regards,
    Community Support Team _ Yingjie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

lbendlin's avatar
lbendlin
Super User
5 years ago

Oh boy, weeks and months.  They do not go well together.

 

Ideally you have a calendar table that associates dates with week numbers.  Even more ideally let's assume a week is confined to a single month.

 

So when you load the february file you would compare it to the calendar table and only accept the week numbers for February.  You can do the same thing for all your source files - get the month number (for example from the file name) and then inner join the file with the calendar table based on the month. Then combine all results into the final table.

 

As soon as you have weeks that stretch across months you are in for a big bag of hurt.