none
Perform total count based on Text.Contains RRS feed

  • Question

  • Hi : 

    I would like to create two custom columns based on column ID and MyList, where I want to count how many ID starting with "R" in the Total ID column and also count how many customer number starting "200" in the next column.

    Thanks

    Thursday, September 5, 2019 6:08 AM

Answers

  • remark: the separator for ID is "," while the separator for MyList is ";"

    let
    
    Source = #table(
    	type table[ID=text, MyList=text],
    	{{"R1-1323,R1-1347,R1-1322","200000062-Q; 200000062-Queensland; 2000005358-Qld"}}
    	),
    	
    Add_Total_ID = Table.AddColumn(Source, "Total ID", each List.Count( List.Select(List.Transform(Text.Split([ID],","), each Text.Upper(Text.Trim(_)) ), each Text.StartsWith(_, "R1")) ), Int64.Type),
    Add_Count_MyList = Table.AddColumn(Add_Total_ID, "Count My List", each List.Count( List.Select(List.Transform(Text.Split([MyList],";"), each Text.Trim(_) ), each Text.StartsWith(_, "200")) ), Int64.Type)
    
    in Add_Count_MyList



    • Edited by anthony34 Thursday, September 5, 2019 7:57 AM
    • Marked as answer by M.Awal Friday, September 6, 2019 8:52 AM
    Thursday, September 5, 2019 7:54 AM
  • Slightly different way:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        func = (text, delim, start) => List.Count(List.Select(Text.Split(text, delim), each Text.StartsWith(_, start))),
        add = Table.AddColumn(Source, "Total ID", each func([ID], ",", "R")),
        add1 = Table.AddColumn(add, "Count MyList", each func([MyList], "; ", "200"))
    in
        add1
    • Marked as answer by M.Awal Friday, September 6, 2019 8:52 AM
    Thursday, September 5, 2019 8:17 AM

All replies

  • remark: the separator for ID is "," while the separator for MyList is ";"

    let
    
    Source = #table(
    	type table[ID=text, MyList=text],
    	{{"R1-1323,R1-1347,R1-1322","200000062-Q; 200000062-Queensland; 2000005358-Qld"}}
    	),
    	
    Add_Total_ID = Table.AddColumn(Source, "Total ID", each List.Count( List.Select(List.Transform(Text.Split([ID],","), each Text.Upper(Text.Trim(_)) ), each Text.StartsWith(_, "R1")) ), Int64.Type),
    Add_Count_MyList = Table.AddColumn(Add_Total_ID, "Count My List", each List.Count( List.Select(List.Transform(Text.Split([MyList],";"), each Text.Trim(_) ), each Text.StartsWith(_, "200")) ), Int64.Type)
    
    in Add_Count_MyList



    • Edited by anthony34 Thursday, September 5, 2019 7:57 AM
    • Marked as answer by M.Awal Friday, September 6, 2019 8:52 AM
    Thursday, September 5, 2019 7:54 AM
  • Slightly different way:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        func = (text, delim, start) => List.Count(List.Select(Text.Split(text, delim), each Text.StartsWith(_, start))),
        add = Table.AddColumn(Source, "Total ID", each func([ID], ",", "R")),
        add1 = Table.AddColumn(add, "Count MyList", each func([MyList], "; ", "200"))
    in
        add1
    • Marked as answer by M.Awal Friday, September 6, 2019 8:52 AM
    Thursday, September 5, 2019 8:17 AM
  • Very good both of you gents. Thanks
    Friday, September 6, 2019 8:52 AM