Forum Discussion

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 "eleg...

Need Help

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.

DarrenLau

Advocate I

7 years ago

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

Super User

7 years ago

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
Advocate I
7 years ago
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
  Advocate I
  7 years ago
  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
    Super User
    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.

Forum Discussion

Transform column data with calculations

Related content

Transform columns / type

TMDL - calculated column

Table Transformation

Transform Column / Type

Transpose/ Transform Rows to Columns