Jan 07 2022 01:31 AM
Hi All,
Hope you are doing well!.. I am trying to extract the matching codes from the column diagcode in my input data with respect to the list of reference diag codes.. The output diag codes are the ones extracted from the column diagcode with respect to the matching reference diag codes...Can you please help me here...
Thanks,
Arun
Jan 07 2022 01:53 AM
That could be
let
Source = Excel.CurrentWorkbook(),
Ref = Source{[Name="Reference"]}[Content],
Reference = Table.TransformColumns(
Ref,
{{"Reference diag code", Text.Trim, type text}}),
Table = Source{[Name="Table1"]}[Content],
#"Removed Columns" = Table.RemoveColumns(Table,{"output diag code"}),
Output = Table.AddColumn(
#"Removed Columns",
"allcodes",
each
Text.Combine(
List.Intersect(
{
List.Transform( Text.Split( [diagcode], ","), each Text.Trim(_) ),
Reference[Reference diag code] }), ","
)
)
in
Output
Jan 07 2022 02:38 AM
@Arun Chandramouli Too late to beat @Sergei Baklan , but as a variant, though less robust solution
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Source2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
RefCode = Source2[Reference diag code],
Split = Table.AddColumn(Source, "Custom", each Text.Split ([diagcode], ",")),
Match = Table.AddColumn(Split, "Output", each List.Intersect ({[Custom], RefCode} )),
Extract = Table.TransformColumns(Match, {"Output", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
#"Removed Columns" = Table.RemoveColumns(Extract,{"Custom"})
in
#"Removed Columns"
Jan 07 2022 04:02 AM
Yes, @Riny_van_Eekelen solution, if practically the same, is more easy in maintenance and debugging. The only I'd add Text.Trim on codes. Based on samples extra spaces are possible.