Hello,I posted this some days ago:https://community.powerbi.com/t5/Desktop/Grouping-by-and-separating-by-year/m-p/2252436#M819467 The answer given in the post is correct, but the fact that it is a virtual table created with measures gives me alot of problems when I try to create dashboards with new columns. In my understanding it would be better to have a table created in Power Query for my specifics needs. The sample data is the same as in the post, with the difference that I no longer need exact matches in the grouping of both years, this means that the desired output is the following:CityStateCustomer TypeRevenue agg 2021Quantity agg 2021Revenue agg 2020Quantity agg 2020MelbourneVICHome Office49.54821.8729SydneyNSWCorporate126.381752.6922SydneyNSWConsumer0.59331.217SydneyNSWSmall Business36.1982.7470MelbourneVICCorporate3.61106nullnullSydneyNSWHome Office4.9216nullnull I think the way to go is:Grouping by City, State,Customer Type and Order Year. Aggregating by the sum of profit margin and order quantity.Doing a Full Outer Join (in the merge queries option) of the the grouped table with itself, with two conditions:Table 1 is the grouped table with the condition that only uses values of Order Year = 2021Table 2 is the grouped table with the condition that only uses values of Order Year = 2020 Now my questions are:How do I group by without overwriting the original table?For example, when I use the merge queries it gives you the option of creating a new table.When doing the Full Outer Join:How do I specify that I need in one table only the data from the current year (and previous year for the other table)?This means that Order Year cannot be exactly a number, it has to represent the current year and previous year. Maybe there is an easier way of doing this but I'm not sure. Any suggestion would be deeply appreciated,Thanks.

Forum Discussion

Anonymous

Not applicable

4 years ago

Solved

Grouping by and conditional merging

Hello,

I posted this some days ago:

https://community.powerbi.com/t5/Desktop/Grouping-by-and-separating-by-year/m-p/2252436#M819467

The answer given in the post is correct, but the fact that it is a virtual table created with measures gives me alot of problems when I try to create dashboards with new columns. In my understanding it would be better to have a table created in Power Query for my specifics needs.

The sample data is the same as in the post, with the difference that I no longer need exact matches in the grouping of both years, this means that the desired output is the following:

City	State	Customer Type	Revenue agg 2021	Quantity agg 2021	Revenue agg 2020	Quantity agg 2020
Melbourne	VIC	Home Office	49.54	82	1.87	29
Sydney	NSW	Corporate	126.38	175	2.69	22
Sydney	NSW	Consumer	0.59	33	1.2	17
Sydney	NSW	Small Business	36.19	8	2.74	70
Melbourne	VIC	Corporate	3.61	106	null	null
Sydney	NSW	Home Office	4.92	16	null	null

I think the way to go is:

Grouping by City, State,Customer Type and Order Year. Aggregating by the sum of profit margin and order quantity.
Doing a Full Outer Join (in the merge queries option) of the the grouped table with itself, with two conditions:
- Table 1 is the grouped table with the condition that only uses values of Order Year = 2021
- Table 2 is the grouped table with the condition that only uses values of Order Year = 2020

Now my questions are:

How do I group by without overwriting the original table?
- For example, when I use the merge queries it gives you the option of creating a new table.
When doing the Full Outer Join:
- How do I specify that I need in one table only the data from the current year (and previous year for the other table)?
- This means that Order Year cannot be exactly a number, it has to represent the current year and previous year.

Maybe there is an easier way of doing this but I'm not sure.

Any suggestion would be deeply appreciated,

Thanks.

Need Help

ronrsnfld

4 years ago

(I will delete my previous answer, as this one is much improved especially with large database)

Try with

Filter for current and previous year
- Place the Filter step as early as your connector will allow.
- Consider Table.Buffer after filtering.
Table.Group: "Order Year","City", "State", "Customer Type"
- Aggregate with Sum of the Revenue (Profit Margin) and Quantity columns.
Unpivot the resultant Attribute and Value columns
Merge the Year and Attribute columns
Pivot on the Merged Columns

Also, omitting the re-sorting operation at the end may also speed things up as that has to re-read the entire table.

let
    Source = Excel.CurrentWorkbook(){[Name="Table9"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Order No", type text}, {"Order Year", Int64.Type}, {"City", type text}, 
        {"State", type text}, {"Customer Type", type text}, {"Order Priority", type text}, 
        {"Product Category", type text}, {"Profit Margin", type number}, {"Order Quantity", Int64.Type}}),

//filter to include only current and previous years
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each 
        [Order Year] = Date.Year(Date.From(DateTime.LocalNow()))
        or [Order Year] = -1 + Date.Year(Date.From(DateTime.LocalNow()))
        ),

//Group and sum
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Order Year","City", "State", "Customer Type"}, {
        {"Revenue", each List.Sum([Profit Margin]),Currency.Type},
        {"Quantity", each List.Sum([Order Quantity]), Int64.Type}

        }),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Grouped Rows", {"Order Year", "City", "State", "Customer Type"}, "Attribute", "Value"),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Unpivoted Columns", {{"Order Year", type text}}, "en-US"),{"Order Year", "Attribute"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
    #"Pivoted Column" = Table.Pivot(#"Merged Columns", List.Distinct(#"Merged Columns"[Merged]), "Merged", "Value")
in
    #"Pivoted Column"

7 Replies

Anonymous

Not applicable

4 years ago

Hi Anonymous

The desired output can be a matrix? Or you have specific request of the column names, order, etc?

Anonymous

Not applicable

4 years ago

Hi Anonymous,

The desired output should be like the following image:

It is a table calculated directly in Power Query.

wdx223_Daniel

Community Champion

4 years ago

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Custom1 = Table.Group(
                          Source,
                          {"City","State","Customer Type"},
                          List.TransformMany(
                                             List.Sort(List.Distinct(Source[Order Year]),1),
                                             each {"Revenue","Qantity"},
                                             (x,y)=>{
                                                     y&" agg "&Text.From(x),
                                                     each List.Sum(
                                                                   Table.Column(
                                                                                Table.SelectRows(
                                                                                                 _,
                                                                                                 (s)=>s[Order Year]=x
                                                                                                ),
                                                                                if y="Revenue"
                                                                                then "Profit Margin"
                                                                                else "Order Quantity"
                                                                               )
                                                                  )
                                                    }
                                            )
                         )
in
    Custom1

ronrsnfld

Super User

4 years ago

Message deleted by author

Anonymous

Not applicable

4 years ago

Hi ronrsnfld ,

The answer that you gave me is exactly what I was looking for, the problem is when I try to replicate the code in my original data (with only a million rows). It uses 90% of my RAM and it hasn't finished yet after an hour and a half. Given that the whole data that I will be using (more than 10 million rows) I'm not sure how to proceed.

ronrsnfld

Super User

4 years ago

(I will delete my previous answer, as this one is much improved especially with large database)

Try with

Filter for current and previous year
- Place the Filter step as early as your connector will allow.
- Consider Table.Buffer after filtering.
Table.Group: "Order Year","City", "State", "Customer Type"
- Aggregate with Sum of the Revenue (Profit Margin) and Quantity columns.
Unpivot the resultant Attribute and Value columns
Merge the Year and Attribute columns
Pivot on the Merged Columns

Also, omitting the re-sorting operation at the end may also speed things up as that has to re-read the entire table.

let
    Source = Excel.CurrentWorkbook(){[Name="Table9"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Order No", type text}, {"Order Year", Int64.Type}, {"City", type text}, 
        {"State", type text}, {"Customer Type", type text}, {"Order Priority", type text}, 
        {"Product Category", type text}, {"Profit Margin", type number}, {"Order Quantity", Int64.Type}}),

//filter to include only current and previous years
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each 
        [Order Year] = Date.Year(Date.From(DateTime.LocalNow()))
        or [Order Year] = -1 + Date.Year(Date.From(DateTime.LocalNow()))
        ),

//Group and sum
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Order Year","City", "State", "Customer Type"}, {
        {"Revenue", each List.Sum([Profit Margin]),Currency.Type},
        {"Quantity", each List.Sum([Order Quantity]), Int64.Type}

        }),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Grouped Rows", {"Order Year", "City", "State", "Customer Type"}, "Attribute", "Value"),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Unpivoted Columns", {{"Order Year", type text}}, "en-US"),{"Order Year", "Attribute"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
    #"Pivoted Column" = Table.Pivot(#"Merged Columns", List.Distinct(#"Merged Columns"[Merged]), "Merged", "Value")
in
    #"Pivoted Column"

Forum Discussion

Grouping by and conditional merging

7 Replies

Related content

Conditional grouping

Conditional Merge

Fabric & Azure Data User Group Program Requirements & Benefits

Grouped summaries with conditional filtering

Community User Group Experience