Create Measure from column values then Extract

[SammiF1244]

I have a columns titled ACTION that holds a value used in an import template. This field is populated based on a series of conditional formulas to determine the action (ADD, MODIFY, DELETE) that is needed. In the formula I have given the reason for the MODIFY action but this is later extracted: 

After the ACTION value is created, I then have to extract out data to allow the ACTION to be specific & work in the import step.

QUESTION: Can I create a measure that uses the original column data before extraction for use in visuals? I have tried to sezrch for this and either I do not know the words to use to find it or there is not any content on this topic. Thanks in advance for your feedback 😄

Here is the code for one of the example queries:

(it's ugly, I know, I am pretty new so it could definately be better 😋, but I am happy with my progress 😍

let
Source = Table.NestedJoin(#"Appended: Final CPT/HCPCS", {"Product_id"}, #"z BHAR Product_Table", {"Product_id"}, "BHAR Product_Table", JoinKind.FullOuter),
#"Expanded BHAR Product_Table1" = Table.ExpandTableColumn(Source, "BHAR Product_Table", {"Product_qualifier", "Product_id", "Product_name", "effective_date", "termination_date", "default_class", "incentive_flag", "generic_product_id", "generic_name", "manufacturer_id", "manufacturer_name", "generic_code", "DEA_class_code", "Therapeutic_class_code", "Therapeutic_equiv_code", "RX_OTC_indicator", "third_party_rest_code", "GPPC_code", "metric_strength", "strength_UOM", "dosage_form", "package_size", "package_UOM", "package_QTY", "total_package_QTY", "legend_change_date", "DESI_code", "maintenance_drug_code", "dispensing_unit_code", "unit_dose_code", "route_admin_code", "form_type_code", "dollar_rank_code", "RX_rank_code", "single_comb_code", "repackager_IND", "product_id.1", "product_name.1", "product_id2", "product_name2", "last_change_date", "drug_status", "int_EXT_Code", "package_description", "OTC_EQUIV_IND", "stc_code", "gcn_code", "HICL_SeqNo", "GTC_Code", "HIC3_Code", "Quadrant_Req", "Tooth_Req", "Surface_Req", "tooth_type", "Auto_Adjudicate", "Clinical_Doc", "Credentialling_Req", "color_code", "hist_multiplier", "min_num_surfaces", "max_num_surfaces", "Time_Units", "asc_grouper", "apc_classification", "base_units", "Default_Unit_Basis", "RoomAndBoardCharges", "Action", "Entity", "Key_1", "Key_2", "Key_3", "Key_4", "Key_5", "Key_6", "Key_7", "Key_8", "Key_9", "Key_10", "DateTimeModified"}, {"Product_qualifier.1", "Product_id.1", "Product_name.1", "effective_date.1", "termination_date.1", "default_class", "incentive_flag", "generic_product_id", "generic_name", "manufacturer_id", "manufacturer_name", "generic_code", "DEA_class_code", "Therapeutic_class_code", "Therapeutic_equiv_code", "RX_OTC_indicator", "third_party_rest_code", "GPPC_code", "metric_strength", "strength_UOM", "dosage_form", "package_size", "package_UOM", "package_QTY", "total_package_QTY", "legend_change_date", "DESI_code", "maintenance_drug_code", "dispensing_unit_code", "unit_dose_code", "route_admin_code", "form_type_code", "dollar_rank_code", "RX_rank_code", "single_comb_code", "repackager_IND", "product_id.1.1", "product_name.1.1", "product_id2", "product_name2", "last_change_date", "drug_status", "int_EXT_Code", "package_description", "OTC_EQUIV_IND", "stc_code", "gcn_code", "HICL_SeqNo", "GTC_Code", "HIC3_Code", "Quadrant_Req", "Tooth_Req", "Surface_Req", "tooth_type", "Auto_Adjudicate", "Clinical_Doc", "Credentialling_Req", "color_code", "hist_multiplier", "min_num_surfaces", "max_num_surfaces", "Time_Units", "asc_grouper", "apc_classification", "base_units", "Default_Unit_Basis", "RoomAndBoardCharges", "Action", "Entity", "Key_1", "Key_2", "Key_3", "Key_4", "Key_5", "Key_6", "Key_7", "Key_8", "Key_9", "Key_10", "DateTimeModified"}),
#"Changed Type - EFF Term Dates" = Table.TransformColumnTypes(#"Expanded BHAR Product_Table1",{{"effective_date.1", type date}, {"termination_date.1", type date}}),
#"Added Custom -% Desc. Match" = Table.AddColumn(#"Changed Type - EFF Term Dates", "% Desc Match", each let
a = List.Distinct(Text.Split([Product_name], " ")),
b = List.Distinct(Text.Split([Product_name.1], " "))
in
List.Count(List.Intersect({a, b})) / List.Count(a)),
#"Added Conditional Column - Mismatch" = Table.AddColumn(#"Added Custom -% Desc. Match", "Mismatch", each if [Key_1] = null then "ADD" else if [Product_id] = null then "DELETE" else if [effective_date] <> [effective_date.1] then "MODIFY (Eff Date)" else if [termination_date] <> [termination_date.1] then "MODIFY (Term Date)" else if [#"% Desc Match"] < 0.80 then "MODIFY (Description)" else "NC (No Change)"),
#"Filtered Rows - CPT & HCPC" = Table.SelectRows(#"Added Conditional Column - Mismatch", each [Product_qualifier.1] = "CPT" or [Product_qualifier.1] = "HCPC" or [Product_qualifier.1] = null),
#"Extracted Text Before Delimiter" = Table.TransformColumns(#"Filtered Rows - CPT & HCPC", {{"Mismatch", each Text.BeforeDelimiter(_, "("), type text}}),
#"Removed Columns - Status & % Disc Match" = Table.RemoveColumns(#"Extracted Text Before Delimiter",{"OPTUM STATUS", "% Desc Match"}),
#"Reordered Columns1" = Table.ReorderColumns(#"Removed Columns - Status & % Disc Match",{"DUPLICATE_FLAG", "Product_qualifier", "Product_id", "Product_name", "effective_date", "termination_date", "Product_qualifier.1", "Product_id.1", "Product_name.1", "effective_date.1", "termination_date.1", "default_class", "incentive_flag", "generic_product_id", "generic_name", "manufacturer_id", "manufacturer_name", "generic_code", "DEA_class_code", "Therapeutic_class_code", "Therapeutic_equiv_code", "RX_OTC_indicator", "third_party_rest_code", "GPPC_code", "metric_strength", "strength_UOM", "dosage_form", "package_size", "package_UOM", "package_QTY", "total_package_QTY", "legend_change_date", "DESI_code", "maintenance_drug_code", "dispensing_unit_code", "unit_dose_code", "route_admin_code", "form_type_code", "dollar_rank_code", "RX_rank_code", "single_comb_code", "repackager_IND", "product_id.1.1", "product_name.1.1", "product_id2", "product_name2", "last_change_date", "drug_status", "int_EXT_Code", "package_description", "OTC_EQUIV_IND", "stc_code", "gcn_code", "HICL_SeqNo", "GTC_Code", "HIC3_Code", "Quadrant_Req", "Tooth_Req", "Surface_Req", "tooth_type", "Auto_Adjudicate", "Clinical_Doc", "Credentialling_Req", "color_code", "hist_multiplier", "min_num_surfaces", "max_num_surfaces", "Time_Units", "asc_grouper", "apc_classification", "base_units", "Default_Unit_Basis", "RoomAndBoardCharges", "Mismatch", "Action", "Entity", "Key_1", "Key_2", "Key_3", "Key_4", "Key_5", "Key_6", "Key_7", "Key_8", "Key_9", "Key_10", "DateTimeModified"}),
#"Removed Columns - original action" = Table.RemoveColumns(#"Reordered Columns1",{"Action"}),
#"Renamed Columns - Mismatch to Action" = Table.RenameColumns(#"Removed Columns - original action",{{"Mismatch", "Action"}}),
#"Replaced Value - Product_qualifier" = Table.ReplaceValue(#"Renamed Columns - Mismatch to Action",null, each [Product_qualifier.1],Replacer.ReplaceValue,{"Product_qualifier"}),
#"Replaced Value - Product_id" = Table.ReplaceValue(#"Replaced Value - Product_qualifier",null, each [Product_id.1],Replacer.ReplaceValue,{"Product_id"}),
#"Replaced Value - Product_name" = Table.ReplaceValue(#"Replaced Value - Product_id",null, each [Product_name.1],Replacer.ReplaceValue,{"Product_name"}),
#"Replaced Value - effective_date" = Table.ReplaceValue(#"Replaced Value - Product_name",null, each [effective_date.1],Replacer.ReplaceValue,{"effective_date"}),
#"Replaced Value - termination_date" = Table.ReplaceValue(#"Replaced Value - effective_date",null, each [termination_date.1],Replacer.ReplaceValue,{"termination_date"}),
#"Removed Columns - remove dup columns" = Table.RemoveColumns(#"Replaced Value - termination_date",{"Product_qualifier.1", "Product_id.1", "Product_name.1", "effective_date.1", "termination_date.1"}),
#"Filtered Rows - term date - rolling 5 years old" = Table.SelectRows(#"Removed Columns - remove dup columns", each ([termination_date] >= #"*Rolling 5 years" or [termination_date] = null)),
#"Sorted Rows - Asc. Action" = Table.Sort(#"Filtered Rows - term date - rolling 5 years old",{{"Action", Order.Ascending}}),
#"Filtered Rows - remove D-codes" = Table.SelectRows(#"Sorted Rows - Asc. Action", each not Text.StartsWith([Product_id], "D")),
#"Filtered Rows - remove NC (no change) rows" = Table.SelectRows(#"Filtered Rows - remove D-codes", each ([Action] <> "NC ")),
#"Filtered Rows - effective_date not null" = Table.SelectRows(#"Filtered Rows - remove NC (no change) rows", each [effective_date] <> null and [effective_date] <> ""),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows - effective_date not null",{"DUPLICATE_FLAG"}),
#"Replaced Value - null to BC 285" = Table.ReplaceValue(#"Removed Columns",null,"285",Replacer.ReplaceValue,{"default_class"}),
#"Replaced Value - tooth type 00 to blank" = Table.ReplaceValue(#"Replaced Value - null to BC 285","00","",Replacer.ReplaceText,{"tooth_type"})
in
#"Replaced Value - tooth type 00 to blank"

[amitchandak]

@SammiF1244 , As long as original columns are present in the table, you can use them in visual. You can create the using switch in a measure

The information you have provided is not making the problem clear to me. Can you please explain with an example.

Appreciate your Kudos.

[SammiF1244]

@amitchandak Thank you for your reply. My issue is the label of the visual only includes the final detaIls:

and I want the 'MODIFY' value (label) to show the parenthetical version from earlier in my steps that I can only see on the 'Transform data" screen:

 Early in the applied steps: (this is what I want for the 'visual' - so I was thinking a measure but can't figure out how to grab the values this early in the transforms steps as it only 'sees' the final output)

Current Situation:

The current label for the visual only reflects the final details after all applied steps under transform:

(this is what I need for the final 'output')

Desired Change:

I'd like to use the value (label) to include the parenthetical information initially presented earlier in the applied steps. This information is currently only visible on the "Transform data" screen when clicking on a step before the extract step:

Explanation:

The additional text in parentheses, "(Desired text)," represents the specific details I want to display from the earlier transformation stage. This information adds essential context to the final data displayed in the visual for dahsboard users, but needs to be excluded in 'table' data.

Technical Considerations:

While I understand that the "Transform data" screen showcases intermediate values not reflected in the final output, I'm exploring the possibility of using a calculated field or measure to access these earlier values and incorporate them into the label. However, I'm unsure of the feasibility of this approach. Perhaps there's an alternative approach I haven't considered??

Thank you for your continued assistance. Please let me know if you need further clarification or if you have any questions.