Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

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


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

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

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Thank you, it works well for both solutions 🙂

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors