Oct 05 2021 01:52 AM
Hi All,
Hope you are doing well....I have a dataset with the combination of visitbillid and contextid denoting an encounter...Every encounter has diagnosis code ,charge codes and modifiers ...Now I have the input data as the diagnosis code seperated by commas , charge code seperated by commas and then the modifiers split by commas ..Now I require the output to have one code per row for diagnosis codes , charge code and modifiers ...Please find attached the sample excel worksheet with the input and the expected output ...Can you please help me here...
Thanks,
Arun
Oct 05 2021 03:33 PM
SolutionWe may group by first two columns without aggregation. Table within each record unpivot, transform texts to lists and combine back as table. The latest could be done by function
( tbl as table) =>
let
#"Removed Other Columns" = Table.SelectColumns(
tbl,
{"Diagnosis code", "Charge code", "Modifiers"}
),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(
#"Removed Other Columns",
{}, "Attribute", "Value"
),
TextsToList = Table.AddColumn(
#"Unpivoted Columns",
"Custom",
each Text.Split([Value], ",")
),
RemoveValueColumn = Table.RemoveColumns(TextsToList,{"Value"}),
localTable = Table.FromColumns(
RemoveValueColumn[Custom],
RemoveValueColumn[Attribute]
)
in
localTable
which is called by main script
let
Source = Excel.CurrentWorkbook(){[Name="Input"]}[Content],
#"Grouped Rows" = Table.Group(
Source,
{"VISITBILLID", "CONTEXTID"},
{ {"Count", each fnSingleTable( _ ) } }
),
#"Expanded Count" = Table.ExpandTableColumn(
#"Grouped Rows",
"Count",
{"Diagnosis code", "Charge code", "Modifiers"},
{"Diagnosis code", "Charge code", "Modifiers"}
)
in
#"Expanded Count"
Result is
Dec 02 2021 10:07 PM
Dec 03 2021 09:52 AM
@Arun Chandramouli , you are welcome, thank you for the feedback
Oct 05 2021 03:33 PM
SolutionWe may group by first two columns without aggregation. Table within each record unpivot, transform texts to lists and combine back as table. The latest could be done by function
( tbl as table) =>
let
#"Removed Other Columns" = Table.SelectColumns(
tbl,
{"Diagnosis code", "Charge code", "Modifiers"}
),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(
#"Removed Other Columns",
{}, "Attribute", "Value"
),
TextsToList = Table.AddColumn(
#"Unpivoted Columns",
"Custom",
each Text.Split([Value], ",")
),
RemoveValueColumn = Table.RemoveColumns(TextsToList,{"Value"}),
localTable = Table.FromColumns(
RemoveValueColumn[Custom],
RemoveValueColumn[Attribute]
)
in
localTable
which is called by main script
let
Source = Excel.CurrentWorkbook(){[Name="Input"]}[Content],
#"Grouped Rows" = Table.Group(
Source,
{"VISITBILLID", "CONTEXTID"},
{ {"Count", each fnSingleTable( _ ) } }
),
#"Expanded Count" = Table.ExpandTableColumn(
#"Grouped Rows",
"Count",
{"Diagnosis code", "Charge code", "Modifiers"},
{"Diagnosis code", "Charge code", "Modifiers"}
)
in
#"Expanded Count"
Result is