Power Query Create group numbering based on Multiple Column

user_1437138 0
Viewed 114

I have a table of the list of companies that have their fruit product sales every year. Also, the data is categorized into multiple quarters of the year. By sample data is as below,

enter image description here

Company Year    Start   End Product Sale
C1  2020    04/01/20 00:00  6/30/2020 00:00 Apple   40
C1  2020    04/01/20 00:00  6/30/2020 00:00 Orange  60
C1  2020    04/01/20 00:00  6/30/2020 00:00 Grape   30
C1  2020    04/01/20 00:00  6/30/2020 00:00 Lemon   20
C1  2020    07/01/20 00:00  9/30/2020 00:00 Orange  82
C1  2020    07/01/20 00:00  9/30/2020 00:00 Lemon   46
C1  2020    07/01/20 00:00  9/30/2020 00:00 Pomegranate 68
C1  2020    07/01/20 00:00  9/30/2020 00:00 Papaya  25
C1  2021    04/01/21 00:00  6/30/2021 00:00 Apple   40
C1  2021    04/01/21 00:00  6/30/2021 00:00 Orange  60
C1  2021    04/01/21 00:00  6/30/2021 00:00 Grape   30
C1  2021    04/01/21 00:00  6/30/2021 00:00 Lemon   21
C1  2021    07/01/21 00:00  9/30/2021 00:00 Orange  82
C1  2021    07/01/21 00:00  9/30/2021 00:00 Lemon   46
C1  2021    07/01/21 00:00  9/30/2021 00:00 Pomegranate 68
C1  2021    07/01/21 00:00  9/30/2021 00:00 Papaya  25
                    
C2  2020    05/01/20 00:00  7/31/2020 00:00 Papaya  25
C2  2020    05/01/20 00:00  7/31/2020 00:00 Orange  65
C2  2020    05/01/20 00:00  7/31/2020 00:00 Lemon   48
C2  2020    05/01/20 00:00  7/31/2020 00:00 Apple   52
C2  2020    08/01/20 00:00  10/30/2020 00:00    Grape   98
C2  2020    08/01/20 00:00  10/30/2020 00:00    Orange  25
C2  2020    08/01/20 00:00  10/30/2020 00:00    Pomegranate 78
C2  2020    08/01/20 00:00  10/30/2020 00:00    Grape   97
C2  2021    05/01/21 00:00  7/31/2021 00:00 Papaya  25
C2  2021    05/01/21 00:00  7/31/2021 00:00 Orange  65
C2  2021    05/01/21 00:00  7/31/2021 00:00 Lemon   48
C2  2021    05/01/21 00:00  7/31/2021 00:00 Apple   52
C2  2021    08/01/21 00:00  10/30/2021 00:00    Grape   98
C2  2021    08/01/21 00:00  10/30/2021 00:00    Orange  25
C2  2021    08/01/21 00:00  10/30/2021 00:00    Pomegranate 78
C2  2021    08/01/21 00:00  10/30/2021 00:00    Grape   97

I wanted to create an index number of every Company, Year, and Quarter. I tried to do this using AddIndexColumn like below.

= Table.Group(#"Sorted Rows2", {"Company","Year","Start"}, {{"Data", each Table.AddIndexColumn(_, "Index", 1, 1), type table}})

The above query transformed my table like the below.

enter image description here

But I want the result like below,

enter image description here

The earliest Quarter of a Company of the year should index as 1 and so on., Sometimes the first quarter of the company would start from 04/01/2021, and the last quarter would begin from 01/01/2022. Then the year of the company in the above quarter will be 2022. I tried many ways to transform the table as above the last screenshot. But nothing was working. It would be really appreciated if I get a proper way of doing this.

We didn't have a field explicitly to indicate when a quarter starts. We should assume the earliest date of a Company of the particular year would be the start of the quarter.

For Example, in the Above Example, the quarter starts from 04/01/2020 for Company 1 of 2020. The Quarter starts on 05/01/2020 for Company 2 of 2020.

powerbi powerquery grouping indexing
3 Answers

Try this instead.

 rank= Table.AddRankColumn(#"Changed Type","Index", {{"Company", Order.Ascending}, {"Year", Order.Ascending}, {"Start", Order.Ascending}}, [RankKind = RankKind.Dense])

enter image description here

user_183450370

You can create a table by Company/Year/Start with index, then merge to original data

let  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Removed Duplicates" = Table.Distinct(Source, {"Company", "Year", "Start"}),
#"Grouped Rows" = Table.Group(#"Removed Duplicates", {"Company", "Year"}, {{"data", each Table.AddIndexColumn(Table.Sort(_,{{"Start", Order.Ascending}}), "Index", 1, 1, Int64.Type), type table }}),
#"Expanded data" = Table.ExpandTableColumn(#"Grouped Rows", "data", {"Start", "Index"}, {"Start", "Index"})
in #"Expanded data"

or you can do all of it in one step, though merge might be faster for larger data sets

Add=Table.AddColumn(#"PriorStepNameHere","New",(i)=> let start = i[Start] in List.PositionOf(List.Sort(List.Distinct(Table.SelectRows(#"PriorStepNameHere", each [Company]=i[Company] and [Year]=i[Year])[Start])),start)+1)
user_92642300

In your desired results table, it appears that you are just returning the quarter of the year, with April being the start of the first quarter.

If that is what you really want, you can just add a column showing the quarter.

For example:

= {4,1,2,3}{Date.QuarterOfYear([Start])-1}

A more extensive desired results table would be better if that is not what you want, and if the previous suggestions do not serve your purposes.

Edit
It seems you want the month number of the earliest date listed for any company to be taken as the first month of the first quarter.

That's a bit more complicated but I think the code below will work.
Please read the code comments, and post back if you have questions.

let

//edit next line to reflect your actual data source
    Source = Excel.CurrentWorkbook(){[Name="Table6"]}[Content],

//set data types
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Company", type text}, {"Year", Int64.Type}, {"Start", type date}, {"End", type date}, {"Product", type text}, {"Sale", Int64.Type}}),

//Group by Company
//Then create a list of twelve month numbers starting at month number of earliest date
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Company"}, {
        {"All", each _, type table [Company=nullable text, Year=nullable number, Start=nullable date, End=nullable date, Product=nullable text, Sale=nullable number]},
        {"Quarters", (t)=>  
             List.Transform(List.Numbers(Date.Month(List.Min(t[Start])),12), each Number.Mod(_ - 1,12)+1), type list
            }
        }),

//Re-expand the grouped table
    #"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"Year", "Start", "End", "Product", "Sale"}),

//Compute quarter number based on position of monthnumber in the generated list
    #"Added Custom" = Table.AddColumn(#"Expanded All", "Quarter", each Number.IntegerDivide(
        List.PositionOf([Quarters],Date.Month([Start])),3)+1, Int64.Type),

//remove unneeded column
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Quarters"})
in 
    #"Removed Columns"

enter image description here

user_28729220
Related