Jan 05 2022 09:46 AM
Hi All,
Hope you are doing well!... I have the input data at a ctextid and a vbillid level...I also have the list of codes input by a human (agentcode) and the number of codes input by an automated algorithm (predicted code)... Now for all the individual codes in the agent code column , I need to find the count of rows in the agentcode column that have the specific code and the count of rows in the predict code column that have matching codes for the specific set of ctextid and vbillid ....Please find attached with the input tab and the needed output tab..Can you please help me here..
Thanks,
Arun
Jan 05 2022 11:22 AM
I did it with power query, but for some reason it doesn´t allow me to attach an excel file.
I can copy and paste the steps in the advance editor if it works for you or if you have an email I can send you the file.
Jan 05 2022 11:26 AM
Sorry, I was able to attach the excel file.
It´s weird, if I have it in my desktop it showed me an error but now it was inside a folder Downloads and it works.
Jan 05 2022 01:17 PM
One more PQ variant is in attached file
Jan 06 2022 05:51 AM
Jan 06 2022 05:53 AM
Jan 06 2022 06:42 AM
Could you please generate sample file for testing of such behaviour. In current one I see nothing > 100% with current PQ logic as well as no agentcode C102 and ctextid:3421 you mentioned.
Jan 07 2022 01:04 AM
@Sergei Baklan :Please find attached the input file where I have modified the input data and also updated the expected output data ; along with the output data from the existing code...Can you please help me here..
Thanks,
Arun
Jan 07 2022 03:55 AM
Thank you for the sample.
Perhaps like this
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
agentList = Table.AddColumn(
Source,
"agentlist",
each List.Transform(
Text.Split([agentcode], "," ),
each Text.Trim(_) )),
predictList = Table.AddColumn(
agentList,
"predictlist",
each List.Transform(
Text.Split([predictcode], "," ),
each Text.Trim(_) )),
#"Expanded agentlist" = Table.ExpandListColumn(
predictList,
"agentlist"),
matching = Table.AddColumn(#"Expanded agentlist",
"Total Matching",
each fnCount( [predictlist], [agentlist] )),
#"Removed Other Columns" = Table.SelectColumns(
matching,
{"agentlist", "Total Matching"}),
#"Grouped Rows" = Table.Group(
#"Removed Other Columns",
{"agentlist"},
{
{"total number", each Table.RowCount(_), Int64.Type},
{"total matching", each List.Max([Total Matching]), type number}}),
percent = Table.AddColumn(
#"Grouped Rows",
"% matching",
each [total matching] / [total number], Percentage.Type)
in
percent
with function
(lst as list, v as any) =>
let
Source = List.Count( List.Select( lst, each _ = v) )
in
Source
Jan 07 2022 04:58 AM
Hi @Sergei Baklan....Really appreciate you getting back...Please find attached the example in which it is failing...I have highlighted the same in the output tab...
Jan 07 2022 05:17 AM
Jan 07 2022 12:40 PM
@alannavarro , @Arun Chandramouli
Perhaps, thank you. But when U071 gives 57%, however 14% is marked as correct result.