Forum Discussion

DarrenLau's avatar
DarrenLau
Advocate I
7 years ago
Solved

Transform column data with calculations

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.

 

 

 

 

Need Help
  • jdbuchanan71's avatar
    jdbuchanan71
    7 years ago

    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.

     

     

     

9 Replies

  • jdbuchanan71's avatar
    jdbuchanan71
    Super User
    7 years ago

    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's avatar
      DarrenLau
      Advocate I
      7 years ago

      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's avatar
        jdbuchanan71
        Super User
        7 years ago

        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?