Transform column data with calculations

[DarrenLau]

Hi,

Seeking the collective wisdom of the community to see if there is a way to automate the following. I know how to do it in Excel and unpivot in PowerBI, just wondering if there is a more "elegant" solution without having a spreadsheet input file that spans 100s of columns.

Scenario : A Sales Quota table is required to map weekly sales achievements against target. Quotas are set by Teams and may be different across quarters.

Preferably, I would like to enter the quarterly target (imported as a file from Excel?) as the following:-

Team | 2019 Q1   | 2019 Q2   | 2019 Q3   | 2019 Q4   | 2020 Q1   | 2020 Q2

TM01 | $100,000  | $120,000  | $130,000  | $120,000  | $115,000  | $125,000

TM02 | $75,000   | $100,000  | $120,000  | $110,000  | $120,000  | $120,000

After import, the resultant table in PowerBI would resemble the following:-

1/Jan/2019  |  TM01  | $7,692.307

1/Jan/2019  |  TM02  | $5,769.231

8/Jan/2019  |  TM01  | $7,692.307

8/Jan/2019  |  TM02  | $5,769.231

15/Jan/2019 |  TM01  | $7,692.307

15/Jan/2019 |  TM02  | $5,769.231

... and so on until ...

25/Jun/2020 |  TM01  | $9,615.385

25/Jun/2020 |  TM02  | $9,230.769

As I mentioned, I have created a table in Excel where the dates (weekly) run across the columns, and the unpivot is performed in PoweBI.

Open to other ideas on how to accomplish the above which is to end up with a table that has individual team targets broken down into a weekly number.

[jdbuchanan71]

Hello @DarrenLau 

Is something like below what you are looking for?  The top is the input excel file and the bottom is the imported and transformed PowerBI.

1. Unpivot Other Colummns to unpivot everything except the team so we get QTR and Quota on rows.
2. Make a custom column that calculates the quarter date using the QTR # * 3 - 2 to get the first month of the qtr plus some other string building steps to generate the date.
3. Delete the QTR column and rename the other 2.

My example file with the excel and the pbix are available here: https://www.dropbox.com/s/l4bswkcc9tc7zws/Quota_example.zip?dl=0

[DarrenLau]

Hi @jdbuchanan71 

That's along the right track, but I want to expand the Quarterly targets into 13 distinct weeks per quarter, hence

1/Jan/2019

8/Jan/2019

15/Jan/2019

and so on.

[jdbuchanan71]

Hello @DarrenLau 

How do you handle the last 'week' of Q1 having 1 day from April?  Starting from 1/1/2019 and incrementing by 7 days the last week starts on 3/26/2019, +7 = 4/2/2019.  Do you leave 4/1 out of the last Q1 week and if so, is the quota amount for that week smaller that the others?

[DarrenLau]

Hi @jdbuchanan71 ,

I haven't really accounted for the overlaps, especially between quarters. Looking back into the past reports, sales that occur on 1 April gets counted against the Q2 Week 1 (or Week 14 according to calendar year) so that hasn't really raised any issue in reporting.

As an aside, all date calculations reference a date table per best practice.

[jdbuchanan71]

If you have a calendar table that has the Quarter Start Date and Week Start Date you can use that to generate the 13 dates you want to divide each quarter into.  Join this to the quota table on the quarter start date and bring in the week start date.  This will replicate the quota 13 times for each person.  Then you just divide the quota by 13 to get the weekly quota.

When we do the join from Quota to Week Start Dates, that is where the 13 rows get added.  Then we divide the quota by 13.

I have updated the sample files with the changes above.  https://www.dropbox.com/s/l4bswkcc9tc7zws/Quota_example.zip?dl=0

[DarrenLau]

Thanks for the help! I am not able to access Dropbox from work (Corporate policy) and will download the file later to check it out.

I have also been playing with generating the row-based data in Excel and then importing the resulting table in to PBI. The individual team files will then be appended into a single table.

Will see which works out easier to manage when the # of teams and years increases.

[DarrenLau]

Hi @jdbuchanan71 ,

Thanks for the PBIX file and I can see (& follow) the steps that you have done. However, I am running into an issue trying to modify the merge statement to refer to my Date table (which is a calculated table from SQLBI.com - https://www.sqlbi.com/articles/reference-date-table-in-dax-and-power-bi/ ) and getting the following error:-

The merge statement is :-

= Table.NestedJoin(#"Changed Type1", {"Qtr_Date"}, "DateTable", {"FW StartOfWeek"}, "Date_Table_Value", JoinKind.LeftOuter)

Not sure what I am doing wrong.

[jdbuchanan71 · Accepted solution]

Hello @DarrenLau ,

The problem is, the date table from SQLBI.com is only in DAX, it is not ever in PowerQuery so other PowerQuery items can't reference it.

Starting with some M code from exceleratorbi.com https://exceleratorbi.com.au/build-reusable-calendar-table-power-query/

I put together a PowerQuery that you can use to get the table you need.  I also put up a quick video on how to add the table to PowerQuery https://www.youtube.com/watch?v=Kr-M8vWv-DE

From PowerBI desktop

• Get Date > Blank Query
• In PowerQuery window click on Advanced Editor
• Paste in the code below and change the StartDate and Enddate to be the range of dates you need to cover the quota period
• Click done
• Rename the query

let
    Source = List.Dates(StartDate, Length, #duration(1, 0, 0, 0)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
    StartDate = #date(2018, 1, 1),
    EndDate = #date(2020, 12, 31),
    Length = Duration.Days(EndDate - StartDate),
    Custom1 = #"Changed Type",
    #"Inserted Start of Quarter" = Table.AddColumn(Custom1, "Start of Quarter", each Date.StartOfQuarter([Date]), type date),
    #"Added Custom2" = Table.AddColumn(#"Inserted Start of Quarter", "day of qtr", each Int16.From ( [Date] - [Start of Quarter]) + 1),
    #"Inserted Integer-Division" = Table.AddColumn(#"Added Custom2", "week of qtr", each if [day of qtr] > 90 then 13 else Number.IntegerDivide([day of qtr] + 6, 7), Int64.Type),
    #"Removed Duplicates" = Table.Distinct(#"Inserted Integer-Division", {"Start of Quarter", "week of qtr"}),
    #"Removed Columns" = Table.RemoveColumns(#"Removed Duplicates",{"day of qtr", "week of qtr"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Start of Quarter", "Date"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Reordered Columns",{{"Start of Quarter", "Quarter Start Date"}, {"Date", "Week Start Date"}})
in
    #"Renamed Columns1"

This will give you the table of [Quarter Start Date] and [Week Start Date] that you need for the earlier solution.

[DarrenLau]

Thanks for the additional help. Wrapping my head around the different ways to do this now.