Search code examples
conditional-statementspowerbifilteringpowerquerym

Power Query - filtering after grouping only in situations where there is more than one same value per one grouped value - is it possible?

Have table in power query:

Insurance Claim No Subcategory Code Approval Date Vendor Name
IE0225873 I_REP_DAM 12.10.2022 XERO
IE0225873 I_OTHER 12.10.2022 NERO
IE0225874 I_REP_DAM 12.10.2022 XERO
IE0225874 I_OTHER 13.10.2022 NERO
IE0225874 I_OTHER 12.10.2022 NERO
IE0225875 I_INS 20.10.2022 XERO
IE0225875 I_REP_DAM 20.10.2022 NERO
IE0225876 I_DAM 20.11.2022 XERO
IE0225876 I_REP 30.12.2022 NERO

Desired table would be:

Insurance Claim No Subcategory Code Approval Date Vendor Name
IE0225873 I_REP_DAM 12.10.2022 XERO
IE0225874 I_REP_DAM 12.10.2022 XERO
IE0225874 I_OTHER 13.10.2022 NERO
IE0225875 I_REP_DAM 20.10.2022 NERO
IE0225876 I_DAM 20.11.2022 XERO
IE0225876 I_REP 30.12.2022 NERO

Firstly I grouped values in my table because I want only one row per one "Insurance Claim No", however there are situations where there can be more than one same "Approval Date" per one "Insurance Claim No" making duplicity rows. When this situation occurs I want to filter rows according to a condition "Subcategory Code" column = "I_REP_DAM". This condition must only apply when there is more than one value of "Approval Date" column per grouped value "Insurance Claim No". If there is only one "Approval Date" per one "Insurance Claim No" there can be any "Subcategory Code". In the example provided the desired result would be first row with "Vendor Name" = "XERO".

Considering also column "Vendor Name" this logic ofc will not work.

I was thinking about logic where with Group By I count the rows for unique "Insurance Claim No" that has more than 1 same date value and then I would add another conditional column saying if I have only one unique date for one unique Insurance Claim No it says "Filter" or if I have more than 1 and "Subcategory Code" = I_REP_DAM then it will be also "Filter" otherwise its "Not filter" and then I can just filter the "Filter". But this logic with Group By hides the "Subcategory Code" so the formula will not work, if I show the "Subcategory Code" there will not be correct counts because there will be Count 1 for every row.

If there would be no "Vendor Name" I could easily solve this problem adding conditional column "IF" saying if "Subcategory Code" = "I_REP_DAM" put 1 otherwise 0, then I grouped all columns without the "Subcategory Code" and as an aggregation I set Sum the conditional column "IF" and this way I always get only one row for one Insurance No with one value of "Approval Date".

Is it possible somehow?

Any hint would be greatly appreciated.

Solution

  • enter image description here

    This is a quick and easy way.

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Insurance Claim No", type text}, {"Subcategory Code", type text}, {"Approval Date", type date}, {"Vendor Name", type text}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Insurance Claim No", Order.Ascending}, {"Subcategory Code", Order.Descending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Insurance Claim No", "Approval Date"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"All", each _, type table [Insurance Claim No=nullable text, Subcategory Code=nullable text, Approval Date=nullable date, Vendor Name=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.First([All])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Insurance Claim No", "Approval Date", "Count", "All"}),
    #"Expanded Custom" = Table.ExpandRecordColumn(#"Removed Columns", "Custom", {"Insurance Claim No", "Subcategory Code", "Approval Date", "Vendor Name"}, {"Insurance Claim No", "Subcategory Code", "Approval Date", "Vendor Name"})
in
    #"Expanded Custom"