cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Vibration85
Helper II
Helper II

Group table in terms of date column and specific value of another column

Hello all,

 

I have a problem which sounds so easy but as I'm a newbie I can't manage it and need your expertise to help me please:

 

As a sample you can have a look at the table below:

I need to remove all the rows which have not Main Category value on their date (in this example I should remove rows 9,10,11), The main category value here is the beginning point for my process per day and if it does not exist in a day, I should remove all the other records on that specific date or ignore them

 

Vibration85_0-1624802246548.png

 

could you please let me know how can I make a DAX code or M-query to fix it? as my data are thousands or millions, needs to have the fastest one.

 

Many thanks 

2 ACCEPTED SOLUTIONS
DataInsights
Super User
Super User

@Vibration85,

 

Try this solution in Power Query.

 

1. Create table MainCategoryDates, consisting of distinct dates for "Main Category" rows.

 

let
    Source = OriginalTable,
    FilterRows = Table.SelectRows(Source, each ([Main column] = "Main Category")),
    RemoveColumns = Table.SelectColumns(FilterRows,{"Date"}),
    RemoveDuplicates = Table.Distinct(RemoveColumns)
in
    RemoveDuplicates

 

2. Merge table MainCategoryDates with the data table using an inner join on the Date column.

 

DataInsights_0-1624814002728.png

 

let
    Source = Table.NestedJoin(OriginalTable, {"Date"}, MainCategoryDates, {"Date"}, "MainCategoryDates", JoinKind.Inner),
    RemoveColumns = Table.RemoveColumns(Source,{"MainCategoryDates"})
in
    RemoveColumns

 

3. Result:

 

DataInsights_2-1624814042837.png

View solution in original post

CNENFRNL
Community Champion
Community Champion

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8k3MzFNwTixJTc8vqlTSUTIyMDLUNTDTNTBUitWJVkovKlAwxC5shCmMyzBDI7guY+zCJsjCFnBhU+zCZmjCsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Main Column" = _t, Date = _t]),
    #"Grouped by Date" = Table.Group(Source, {"Date"}, {{"res", each if List.PositionOf([Main Column], "Main Category")<>-1 then _ else null}}),
    #"Expanded Rows" = let cols=Table.ColumnNames(Source) in Table.ExpandTableColumn(Table.SelectRows(Table.RemoveColumns(#"Grouped by Date", "Date"), each not([res] is null)), "res", cols, cols)
in
    #"Expanded Rows"

Screenshot 2021-06-27 200741.png

View solution in original post

7 REPLIES 7
CNENFRNL
Community Champion
Community Champion

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8k3MzFNwTixJTc8vqlTSUTIyMDLUNTDTNTBUitWJVkovKlAwxC5shCmMyzBDI7guY+zCJsjCFnBhU+zCZmjCsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Main Column" = _t, Date = _t]),
    #"Grouped by Date" = Table.Group(Source, {"Date"}, {{"res", each if List.PositionOf([Main Column], "Main Category")<>-1 then _ else null}}),
    #"Expanded Rows" = let cols=Table.ColumnNames(Source) in Table.ExpandTableColumn(Table.SelectRows(Table.RemoveColumns(#"Grouped by Date", "Date"), each not([res] is null)), "res", cols, cols)
in
    #"Expanded Rows"

Screenshot 2021-06-27 200741.png

View solution in original post

May I ask if I want to add another column to have the condition based on that as well, how can I extend this query?  I have another column called category_id, and I want to check if we have the Main category check the category_id of all these group1, group2, ... and if their category_ids is the same as the Main category then accumulate prices of them:

Vibration85_0-1624887538705.png

 

 

For the above example if group 1, group 2 has the same category_id as the Main category, 

 

I need to sum up for Feb 12th prices of Main category + group 1 + group 2 = 1290+340+367

 

@Vibration85,

 

You can use Group By in Power Query. See step GroupRows:

 

let
  Source = OriginalTable,
  GroupByDate = Table.Group(
    Source,
    {"Date"},
    {{"res", each if List.PositionOf([Main column], "Main Category") <> - 1 then _ else null}}
  ),
  ExpandRows =
    let
      cols = Table.ColumnNames(Source)
    in
      Table.ExpandTableColumn(
        Table.SelectRows(Table.RemoveColumns(GroupByDate, "Date"), each not ([res] is null)),
        "res",
        cols,
        cols
      ),
  ChangeType = Table.TransformColumnTypes(
    ExpandRows,
    {{"ID", Int64.Type}, {"Date", type date}, {"Price", Int64.Type}}
  ),
  GroupRows = Table.Group(
    ChangeType,
    {"Date", "category_id"},
    {{"Sum Price", each List.Sum([Price]), type number}}
  )
in
  GroupRows

 

DataInsights_0-1624921169485.png

 

@DataInsights   thanks a lot, it works well and now I learned how can I group-by a table with one or more conditions ... that was what exactly I wanted 🙂 

 

Many thanks, it works well 🙂 

DataInsights
Super User
Super User

@Vibration85,

 

Try this solution in Power Query.

 

1. Create table MainCategoryDates, consisting of distinct dates for "Main Category" rows.

 

let
    Source = OriginalTable,
    FilterRows = Table.SelectRows(Source, each ([Main column] = "Main Category")),
    RemoveColumns = Table.SelectColumns(FilterRows,{"Date"}),
    RemoveDuplicates = Table.Distinct(RemoveColumns)
in
    RemoveDuplicates

 

2. Merge table MainCategoryDates with the data table using an inner join on the Date column.

 

DataInsights_0-1624814002728.png

 

let
    Source = Table.NestedJoin(OriginalTable, {"Date"}, MainCategoryDates, {"Date"}, "MainCategoryDates", JoinKind.Inner),
    RemoveColumns = Table.RemoveColumns(Source,{"MainCategoryDates"})
in
    RemoveColumns

 

3. Result:

 

DataInsights_2-1624814042837.png

View solution in original post

Thank you, it works well for both solutions 🙂

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors