Results 1 to 2 of 2

Thread: PQ - counting rows filtered and after filtering

  1. #1
    Sir Moderator sandy666's Avatar
    Join Date
    May 2018
    Posts
    238
    Rep Power
    7

    Cool PQ - counting rows filtered and after filtering

    Code:
    // FName
    let
        Source = Table.Distinct(Table.SelectRows(Excel.CurrentWorkbook(){[Name="FName"]}[Content], each [FName] <> null and [FName] <> ""))
    in
        Source
    Code:
    // FRegion
    let
        Source = Table.Distinct(Table.SelectRows(Excel.CurrentWorkbook(){[Name="FRegion"]}[Content], each [FRegion] <> null and [FRegion] <> ""))
    in
        Source
    Code:
    // FCountry
    let
        Source = Table.Distinct(Table.SelectRows(Excel.CurrentWorkbook(){[Name="FCountry"]}[Content], each [FCountry] <> null and [FCountry] <> ""))
    in
        Source
    Code:
    // src
    let
        src = [
        Source = Csv.Document(File.Contents("D:\test\countrows\data-v9i8Rbfh1ul6_1diL56pb.csv"),[Delimiter=",", Columns=3, Encoding=65001, QuoteStyle=QuoteStyle.None]),
        TPH = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    
        Fname = Table.AddColumn(TPH, "Fname", each FName),
        Exp = Table.ExpandTableColumn(Fname, "Fname", {"FName"},  {"fname"} ),
        TCname = Table.AddColumn(Exp, "TCN", each Text.Contains([name],[fname],Comparer.OrdinalIgnoreCase)),
        TSR_true = Table.SelectRows(TCname, each ([TCN] = true)),
        Fname_left = Table.NestedJoin(TPH, {"name"}, TSR_true, {"name"}, "TSR_true", JoinKind.LeftAnti),
    
        Fregion = Table.AddColumn(Fname_left, "Fregion", each FRegion),
        Exp1 = Table.ExpandTableColumn(Fregion, "Fregion", {"FRegion"}, {"fregion"}),
        TCregion = Table.AddColumn(Exp1, "TCR", each Text.Contains([region],[fregion],Comparer.OrdinalIgnoreCase)),
        TSR_true1 = Table.SelectRows(TCregion, each ([TCR] = true)),
        Fregion_left = Table.NestedJoin(TCregion, {"region"}, TSR_true1, {"region"}, "TSR_true1", JoinKind.LeftAnti),
    
        Fcountry = Table.AddColumn(Fregion_left, "fcountry", each FCountry),
        Exp2 = Table.ExpandTableColumn(Fcountry, "fcountry", {"FCountry"}, {"FCountry"}),
        TCcountry = Table.AddColumn(Exp2, "TCC", each Text.Contains([country], [FCountry], Comparer.OrdinalIgnoreCase)),
        TSR_true2 = Table.SelectRows(TCcountry, each ([TCC] = true)),
        Fcountry_left = Table.NestedJoin(TCcountry, {"country"}, TSR_true2, {"country"}, "TSR_true2", JoinKind.LeftAnti)
        ]
    in
        src

    Code:
    // Count
    let
        Count = Text.Format("Total: #[T]#(lf) Name: #[N]#(lf) Region: #[R]#(lf) Country: #[C]",
        [
        T = Text.From(Table.RowCount(src[TPH])),
        N = Text.From(Table.RowCount(src[TSR_true]))&" filtered / left "&Text.From(Table.RowCount(src[Fname_left])),
        R = Text.From(Table.RowCount(src[TSR_true1]))&" filtered / left "&Text.From(Table.RowCount(src[Fname_left])-Table.RowCount(src[TSR_true1])),
        C = Text.From(Table.RowCount(src[TSR_true2]))&" filtered / left "&Text.From(Table.RowCount(src[Fname_left])-Table.RowCount(src[TSR_true1])-Table.RowCount(src[TSR_true2]))
        ]),
        C2T = #table(1, {{Count}}),
        SplitR = Table.ExpandListColumn(Table.TransformColumns(C2T, {{"Column1", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1"),
        Ren = Table.RenameColumns(SplitR,{{"Column1", "Count"}})
    in
        Ren
    Filters:
    FName FRegion FCountry
    rr sh us
    ll


    Result:
    Count
    Total: 100
    Name: 20 filtered / left 80
    Region: 2 filtered / left 78
    Country: 3 filtered / left 75
    Attached Files Attached Files
    Last edited by sandy666; 09-03-2023 at 03:21 PM. Reason: update
    sandy
    I know you know but I forgot my Crystal Ball and don't know what you know also I may not remember what I did weeks, months or years ago so answer asap. Thinking doesn't hurt

  2. #2
    Sir Moderator sandy666's Avatar
    Join Date
    May 2018
    Posts
    238
    Rep Power
    7
    later

    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg
    https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNsaS3Lp1
    https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgR1EPUkhw
    https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNe_XC-jK
    https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNPOdiDuv
    https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgN7AC7wAc
    https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgyBACXgNY4j_cHgH5J4AaABAg.9oTkVdzfqfm 9wlhQrYJP3M
    https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgxYgiEZuS9I3xkjJv54AaABAg
    https://www.youtube.com/watch?v=DVFFApHzYVk&lc=Ugyi578yhj9zShmhuPl4AaABAg
    https://www.youtube.com/watch?v=GqzeFYWjTxI&lc=UgxvxlnuTRWiV6MUZB14AaABAg
    https://www.youtube.com/watch?v=_8i1fVEi5WY&lc=Ugz0ptwE5J-2CpX4Lzh4AaABAg
    https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgxoHAw8RwR7VmyVBUt4AaABAg. 9C-br0lEl8V9xI0_6pCaR9
    https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=Ugz5DDCMqmHLeEjUU8t4AaABAg. 9bl7m03Onql9xI-ar3Z0ME
    https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgxYnpd9leriPmc8rPd4AaABAg. 9gdrYDocLIm9xI-2ZpVF-q
    https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgyjoPLjNeIAOMVH_u94AaABAg. 9id_Q3FO8Lp9xHyeYSuv1I
    https://www.reddit.com/r/windowsxp/comments/pexq9q/comment/k81ybvj/?utm_source=reddit&utm_medium=web2x&context=3
    https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgxYgiEZuS9I3xkjJv54AaABAg
    https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgyBACXgNY4j_cHgH5J4AaABAg.9oTkVdzfqfm 9wlhQrYJP3M
    ttps://www.youtube.com/watch?v=LP9fz2DCMBE
    https://www.youtube.com/watch?v=LP9fz2DCMBE&lc=UgzbPgJUMCztIOQDym14AaABAg
    https://www.youtube.com/watch?v=LP9fz2DCMBE&lc=UgzbPgJUMCztIOQDym14AaABAg. 9wdo_rWgxSH9wdpcYqrvp8
    ttps://www.youtube.com/watch?v=bFxnXH4-L1A
    https://www.youtube.com/watch?v=bFxnXH4-L1A&lc=UgxuODisjo6cvom7O-B4AaABAg.9w_AeS3JiK09wdi2XviwLG
    https://www.youtube.com/watch?v=bFxnXH4-L1A&lc=UgxBU39bTptFznDC1PJ4AaABAg
    ttps://www.youtube.com/watch?v=GqzeFYWjTxI
    https://www.youtube.com/watch?v=GqzeFYWjTxI&lc=UgwJnJDJ5JT8hFvibt14AaABAg
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=314837#p314837
    https://www.eileenslounge.com/viewtopic.php?f=21&t=40701&p=314836#p314836
    https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314621#p314621
    https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314619#p314619
    https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314600#p314600
    https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314599#p314599
    https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314274#p314274
    https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314229#p314229
    https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314195#p314195
    https://www.eileenslounge.com/viewtopic.php?f=36&t=39706&p=314110#p314110
    https://www.eileenslounge.com/viewtopic.php?f=30&t=40597&p=314081#p314081
    https://www.eileenslounge.com/viewtopic.php?f=30&t=40597&p=314078#p314078
    https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=314062#p314062
    https://www.eileenslounge.com/viewtopic.php?f=30&t=40597&p=314054#p314054
    https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313971#p313971
    https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=313909#p313909
    https://www.eileenslounge.com/viewtopic.php?f=27&t=40574&p=313879#p313879
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    Last edited by DocAElstein; 02-29-2024 at 09:45 PM.
    sandy
    I know you know but I forgot my Crystal Ball and don't know what you know also I may not remember what I did weeks, months or years ago so answer asap. Thinking doesn't hurt

Similar Threads

  1. VBA - Count filtered rows in the table
    By sandy666 in forum Excel Help
    Replies: 50
    Last Post: 08-26-2023, 09:35 PM
  2. PQ - Filtering by filenames
    By sandy666 in forum Power Query, Power Pivot and Power BI
    Replies: 0
    Last Post: 02-26-2021, 12:00 PM
  3. Subtotal with Filtering
    By Sakalansalex in forum Excel Help
    Replies: 2
    Last Post: 10-25-2017, 02:54 PM
  4. Delete Filtered Rows Excluding The Header
    By xander1981 in forum Excel Help
    Replies: 5
    Last Post: 04-01-2014, 11:44 PM
  5. Drop-down list (with filtering)
    By mahmoud-lee in forum Excel Help
    Replies: 2
    Last Post: 02-22-2014, 12:14 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •