sandy666
08-22-2023, 09:46 PM
// FName
let
Source = Table.Distinct(Table.SelectRows(Excel.CurrentWorkb ook(){[Name="FName"]}[Content], each [FName] <> null and [FName] <> ""))
in
Source
// FRegion
let
Source = Table.Distinct(Table.SelectRows(Excel.CurrentWorkb ook(){[Name="FRegion"]}[Content], each [FRegion] <> null and [FRegion] <> ""))
in
Source
// FCountry
let
Source = Table.Distinct(Table.SelectRows(Excel.CurrentWorkb ook(){[Name="FCountry"]}[Content], each [FCountry] <> null and [FCountry] <> ""))
in
Source
// 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
// 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:
FNameFRegionFCountry
rrshus
ll
Result:
Count
Total: 100
Name: 20 filtered / left 80
Region: 2 filtered / left 78
Country: 3 filtered / left 75
let
Source = Table.Distinct(Table.SelectRows(Excel.CurrentWorkb ook(){[Name="FName"]}[Content], each [FName] <> null and [FName] <> ""))
in
Source
// FRegion
let
Source = Table.Distinct(Table.SelectRows(Excel.CurrentWorkb ook(){[Name="FRegion"]}[Content], each [FRegion] <> null and [FRegion] <> ""))
in
Source
// FCountry
let
Source = Table.Distinct(Table.SelectRows(Excel.CurrentWorkb ook(){[Name="FCountry"]}[Content], each [FCountry] <> null and [FCountry] <> ""))
in
Source
// 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
// 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:
FNameFRegionFCountry
rrshus
ll
Result:
Count
Total: 100
Name: 20 filtered / left 80
Region: 2 filtered / left 78
Country: 3 filtered / left 75