topic Re: Group table in terms of date column and specific value of another column in DAX Commands and Tips

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

Anonymous — Sun, 27 Jun 2021 14:16:15 GMT

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

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

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

DataInsights — Sun, 27 Jun 2021 17:21:20 GMT

Anonymous,

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.

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

3. Result:

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

CNENFRNL — Sun, 27 Jun 2021 18:08:11 GMT

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"

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

Anonymous — Mon, 28 Jun 2021 06:12:29 GMT

Many thanks, it works well 🙂

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

Anonymous — Mon, 28 Jun 2021 06:13:16 GMT

Thank you, it works well for both solutions 🙂

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

Anonymous — Mon, 28 Jun 2021 13:38:55 GMT

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:

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

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

DataInsights — Mon, 28 Jun 2021 23:00:30 GMT

Anonymous,

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

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

Anonymous — Tue, 29 Jun 2021 12:45:50 GMT

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 🙂