Excel

Acts as a Senior Data Architect expert in Microsoft Excel and applied computational logic. Your goal is to design an ultra-advanced conditional logic structure for [Automatic Category Classification] of a massive data set located in the range [Input_Range]. The solution must transcend the limitations of simple nesting, integrating matrix and dynamic search functions to guarantee full scalability and surgical precision in automatic decision making based on the crossed values ​​of [Criteria_Column_1], [Criteria_Column_2] and [Criteria_Column_3].


Develop a master formula that uses the LET function to optimize the performance of the calculation engine, defining internal variables that evaluate whether each record meets the specific [Critical_Threshold] and [Warning_Threshold] thresholds. The logic should prioritize the categories as follows: if the 'Maximum Alert' condition is met through cross-validation with the reference table [Master_Validation_Table], automatically assign the label '[Priority_Label_A]'; Otherwise, proceed to a multidimensional evaluation that combines logical operators AND (AND) and OR (OR) to determine if the record belongs to '[Label_Segment_B]' or '[Label_Segment_C]'.


It implements a robust exception handling layer using the IF.ERROR function and a non-case protocol that returns the value '[Unclassified_Value]' if the input data is null, inconsistent, or presents #N/A errors. The formula should be able to compare the value of the current cell against the moving average of the [Historical_Reference_Range] range using conditional aggregation functions such as AVERAGEIF SET, allowing the sorting to be dynamic and automatically recalibrate when the global data in the environment changes. The final result should be a technical text string that includes a code prefix extracted from the [Code_Table].


Finally, it provides a detailed technical explanation of the evaluation hierarchy used, ensuring that any modification to the parameters in the 'Settings' tab is instantly reflected in the [Final_Result_Column]. This structure must be optimized to process more than 50,000 records without compromising the latency of the Excel workbook, avoiding the use of volatile functions such as INDIRECT or OFFSET to maintain the stability of the professional analytical model.

If any key information needed to fill the bracketed fields is missing, ask me the necessary questions before answering.

Acts as a senior expert in Business Intelligence and specialist in Power Query (M Language). Your goal is to design an advanced, automated workflow for data extraction from the [URL_DEL_SITIO_WEB] portal in order to capture critical information about [TIPO_DE_DATOS_A_EXTRAER]. The system must be prepared to handle not only standard HTML tables, but also unstructured structures based on <div>, <span> tags or <ul> lists that require deep navigation using the 'Web.Contents' connector and the 'Html.Table' function.


The process should start with parameterizing the URL to allow dynamic extraction based on [VARIABLE_DE_FILTRO_O_PAGINACION]. I need the Power Query script to be able to loop over a range of pages defined by [NUMERO_TOTAL_DE_PAGINAS], using a custom function that invokes the web structure recursively or through a generated list. You must detail how to manage HTTP headers, including the 'User-Agent', to ensure that the request is accepted by the external server without being blocked by security policies.


Once the raw response is obtained, the flow must apply a series of rigorous transformations: first, the expansion of nested records; second, cleaning special characters and non-printable whitespace using 'Text.Clean' and 'Text.Trim'; and third, data type conversion by automatically detecting currency formats, international dates, and numeric values ​​with [REGION_GEOGRAFICA]-specific thousands separators. It is imperative that the script includes robust error handling logic using 'try...otherwise' so that in the event that a visual element of the web has changed, the query does not fail and return a null value or a controlled warning message.


Finally, the result should be a perfectly structured master table in Excel that connects to the Data Model (Power Pivot). This table should include calculated metadata columns, such as [FECHA_DE_EXTRACCION] and [URL_ORIGEN_ESPECIFICA], to ensure information traceability. The design must be optimized so that the update (Refresh) is efficient, minimizing the load on the source server and maximizing the processing speed on the local client, preparing it for subsequent analysis in the [NOMBRE_DEL_REPORTE] project.

If any key information needed to fill the bracketed fields is missing, ask me the necessary questions before answering.