PQ - counting rows filtered and after filtering

**sandy666** · 08-22-2023, 09:46 PM

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: