Dynamic Week Appending

[Anonymous]

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 Contains monthofWeekly sales.

Lets say that I'm in January and as I said Company sends me the 'Weekly January' file. But in 'Weekly February' file , the data contains Weeks and data of January and February. 

But in 'WeeklyFebruary' file, the file contains Updated Values of previous months Weeks. - The Company dont want that.

They want historical data with previousMonth's Weekly Values (from previous month's file) + this months Weekly Values with Dynamically.

I uploaded some example files for better explanation.

 

This is the Data in January. Date Contains WeekKey for Dates.

 

This is the Data in February.

This i the Data we want. 

I hope this will help you to help me 🙂

Thanks for your time. Take Care !

[v-yingjl · Accepted solution]

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.

[amitchandak]

@Anonymous , I did not got it completely. But you can get week start(Monday) like this

A new column

Week to Date = var _st = date(right([Week],4),1,1)
var _week = left([Week],2)*1
Return _st+((_week-1)*7) -WEEKDAY(_st,2)+1

This migh not fall in same month if month start in between

WEEKDAY(_st,1) will give sun day week start

and +7 to above formula will give you week end.

You can use week start and end to get to correct month

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...

[AllisonKennedy]

@Anonymous  As @lbendlin  mentioned, weeks and months aren't easy to reconcile. When you say they want this month's data only and historical values from previous months, how do you decide which weeks belong in 'this month'. It is possible to filter the sample files when you get data from folder, so I would suggest storing all these monthly files in the same folder (ideally on SharePoint) then use Power BI to get data from folder and append all files. We will need to tell Power BI which month you want to keep from each file, so name each file with the Month Year that you want kept from that file, such as "2020.01" for January 2020.

The problem as you have explained, is that you'll have multiple rows all previous weeks and you only want the ORIGINAL values from each week. So that means you need to filter the files BEFORE the append. 

When you do Get Data > From Folder, Power BI creates a 'Transform Sample File' query. Click into this query and do a 'Relative Date Filter' on the Week Key column. Well, actually your Week Key column doesn't look to be in date format, so you will need to Add Column to create a Date column. If you can tell us how you decide which month a week falls into we can help create the best Date column from your week key (ie do you use start or end of week to decide what month it falls in and does your week start Sunday, Monday or other?). Once you have a Date data type column, you can filter it to include only rows for This Month. 

Now, we will introduce a parameter to replace the value of 'This Month'. Remember you have named the files with the month of data you want to keep from each file, so that will be your Parameter value. Follow similar steps as in this blog to create the parameter from your Query and use the custom function to apply the parameter. If you're new to Power BI, this can be confusing the first couple times you do it, so if you can provide sample files/data we can be more helpful. Use Parameters to Combine Data 

[lbendlin]

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.