La mejor manera de analizar texto dentro de Power Query

Anónimo

Un colega mío ha escrito una solución de Power Apps para el negocio que hace un montón de preguntas y almacena los resultados en Sharepoint. Sin embargo, los datos capturados por esta solución de Power Apps se almacenan en un solo campo.

El formato usado para separar cada pregunta es ‘##’ (sin comillas) y luego usa comas para separar cada parte de los detalles de la respuesta. Contiene valores como ‘Número de pregunta’, ‘Pregunta formulada’, ‘Comentarios’ y cada cuadro de respuesta (es decir, Temp. máx., Temp. mín., Valor de ajuste). Cada pregunta sigue el mismo formato, simplemente el texto ‘Pregunta formulada’ puede diferir. Los cuadros de comentarios y respuestas son siempre del mismo tipo de datos y nombre. Aparte de los ‘Comentarios’, no se admiten comas en el texto almacenado.

La solución que escribí para hacer el trabajo funciona pero se siente… lenta. Tengo curiosidad por saber si alguien ha encontrado un método más optimizado. Mi método fue el siguiente:

Tome todo el texto, fila por fila, y divídalo en 1 tabla por cada pregunta.

Para cada pregunta (tabla), realice otra operación de análisis para dividir cada respuesta en su propia columna. Utilizo una combinación de divisiones de primera coma y última coma para evitar cualquier coma adicional en la sección de comentarios. No se está produciendo ningún ajuste de datos. Cada columna dividida recibe un nombre correspondiente a sus datos (es decir, Comentarios, Temp. Máx., Temp. Mín.).

Por último, fusiono todas las tablas de preguntas para dar una lista completa de respuestas sin pivotar.

Esto me da los datos que necesito para hacer todas las bondades de Power BI, pero la solución está totalmente codificada. Si alguien solicita que se agregue una nueva pregunta, todo debe replicarse a mano. Si algo cambia en la estructura de la pregunta, hay mucha edición manual. Esto también hace que la interfaz de usuario de Power Query se detenga durante mucho tiempo, ya que se vuelve a calcular después de cada cambio. ¡Mi reino para un bucle y una matriz tridimensional!

Interesado en otras soluciones!

Hola @Anonimo,

por favor verifique este método:

let
    Questions = {   "Question Number",
                    "Therm#",
                    "Location",
                    "Min Calc",
                    "Max Calc",
                    "Therm temp Calc",
                    "Oven Display Temp Calc",
                    "Reading Calibration variable"},
    Source = "1,MIC85Q,Phycology Lab,3,4,N/A,N/A,-0.05,##2,RN687Q,Oven in volatiles prep,N/A,N/A,22,24,-3.95,##3,ORG160Q,Volatiles Prep Lab,3,4,N/A,N/A,0.3,##4,EN541Q,Waters Lab Freezer,2,4,N/A,N/A,0.6,##5,EN723Q,Waters Lab Cold Room,6,8,N/A,N/A,-1.2,##6,EN757Q,Byth St Cold Room,2.2,2.3,N/A,N/A,-1.45,##7,EN758Q,Byth St Cold Room,1.0,1.3,N/A,N/A,-1.65,##8,EN759Q,Inorgs Lab Reagent Fridge,5,6,N/A,N/A,-0.95,##9,EN382Q,BOD Incubator,34,35,N/A,N/A,-0.1,##10,EN536Q,On the wall in BOD area,3,5,N/A,N/A,0.4,##11,EN724Q,Sample fridge in Metals water prep room,2,4,N/A,N/A,-1.4,##12,EN879Q,Solids Oven,N/A,N/A,33,34,3.25,##13,EN920Q,Solids Oven,N/A,N/A,24,25,1.9,##14,EN660Q,40° Moisture oven,N/A,N/A,40,42,-1.45,##15,EN549Q,Dust Oven,N/A,N/A,34,N/A,-7.6,##16,EN287Q,Solids Oven,N/A,N/A,45,46,0,##17,EN804Q,O&G Oven,N/A,N/A,45,N/A,-3.9,##18,EN919Q,Moisture oven in TCLP room,N/A,N/A,36,36,0.95,##19,EN361Q,On the wall in the TCLP lab (read IN screen),2,3,N/A,N/A,-0.85,##20,EN380Q,Fridge in dioxins soil prep room,3,6,N/A,N/A,0.3,##21,EN381Q,Freezer in dioxins soil prep room,4,5,N/A,N/A,0.05,##22,EN188Q,Left oven in dioxins soil prep room,N/A,N/A,55,N/A,-4.5,##23,EN407Q,Right oven in dioxins soil prep room,N/A,N/A,45,N/A,3.3,##24,EN289Q,Oven in dioxins water prep room,N/A,N/A,46,45,-0.35,##25,EN606Q,Oven (1st on right) in outside prep area,N/A,N/A,64,54,0.5,##26,ORG118Q,Column Leaching room,4,5,N/A,N/A,0.25,##27,MIC21Q,Micro Fridge (In Food / Pharma Room),2,3,N/A,N/A,1.9,##28,MIC23Q,Micro Air Temp (directly inside 3rd door),1,2,N/A,N/A,0.2,##29,MIC86Q,Micro Fridge (1st),2,3,N/A,N/A,0.45,##30,MIC25Q,Micro Refrigerated incubator,N/A,N/A,N/A,55,-1.3,##31,MIC42Q,Micro Incubator along wall (1st),N/A,N/A,N/A,45,-1.65,##32,MIC26Q,Micro Incubator&nbsp; along wall (2nd),N/A,N/A,N/A,53,0,##33,MIC27Q,Micro Incubator along wall (3rd),N/A,N/A,N/A,67,0.4,##34,MIC87Q,Micro Incubator along wall (4th),N/A,N/A,N/A,75,0.1,##35,MIC109Q,Micro Incubator along wall (5th),N/A,N/A,N/A,65,0,##36,MIC83Q,Micro Incubator under Fume Hood,N/A,N/A,N/A,67,0.3,##37,MIC61Q,Micro incubator under bench (left),N/A,N/A,N/A,46,-1.2,##38,MIC64Q,Micro incubator under bench (right),N/A,N/A,N/A,56,0.1,##39,EN682Q,Chest Freezer in Sample Receipt,34,44,N/A,N/A,0.7,##40,EME033Q,Glass Door Fridge in Sample Receipt,2,3,N/A,N/A,1.1,##41,EN647Q,3 Door Freezer in Sample Receipt,4,5,N/A,N/A,-0.4,##42,EN735Q,Upright Freezer in Sample Receipt,4,5,N/A,N/A,-0.05",
    SplitText = Text.Split(Source, "##"),
    ToTable = Table.FromList(SplitText, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Trimmed Text" = Table.TransformColumns(ToTable,{{"Column1", each Text.TrimEnd(_, ","), type text}}),
    MyFunction = (MyText as text, Separator as text) => Table.FromRows({Text.Split(MyText, Separator)}, Questions),
    #"Added Custom" = Table.AddColumn(#"Trimmed Text", "Custom", each MyFunction([Column1], ",")),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", Questions)
in
    #"Expanded Custom"

Reemplace la expresión del paso «MyFunction» con su código de función específico para la separación de texto.

Este método realiza el «bucle» simplemente en una tabla con una fila por pregunta.

Salud,

Imke Feldmann

www.ElBiccontador.com — Cómo integrar el código M en su solución — Consulte más recursos de aprendizaje de PBI aquí

Anónimo

@ImkeFThis funcionó tan brillantemente como sugiere su reputación. Aquí está la solución final en caso de que esto sea útil para otros:

Modificamos ligeramente el formato para corregir ciertos problemas. En primer lugar, envolvimos todos los valores en ‘ para permitir que existan comas en el texto. Por último, el formato estaba colocando incorrectamente el delimitador al final de la cadena, que eliminé.

Para hacer de esta una plataforma repetible, primero creé algunas constantes, en su propia carpeta. Ellos eran:
c_QuestionComponentList (Edite esto para su propio formato)

() =>
let
    Questions = {   "Question Number",
                    "Therm#",
                    "Location",
                    "Min Calc",
                    "Max Calc",
                    "Therm temp Calc",
                    "Oven Display Temp Calc",
                    "Reading Calibration variable",
                    "Comments"
    }
in
    Questions

c_QuestionComponentDelimiter (Esto incluye los caracteres de ajuste)

let
    Source = "','"
in
    Source

c_QuestionDelimiter

let
    Source = "##"
in
    Source

switch_Wrap (Me permite apagar y encender si se está usando una envoltura. 0 para No, 1 para Sí)

let
    Source = 1
in
    Source

Anónimo

Ahora para las funciones que usaremos:

fnDividir preguntas

(QuestionText as text) =>
let 
    SplitText = Text.Split(QuestionText, c_QuestionDelimiter),
    ToTable = Table.FromList(SplitText, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Trimmed Text" = if switch_Wrap = 1 then Table.TransformColumns(ToTable,{{"Column1", each Text.End(_, Text.Length(_) - 1), type text}}) else ToTable,
    #"Trimmed Text2" = if switch_Wrap = 1 then Table.TransformColumns(#"Trimmed Text",{{"Column1", each Text.Start(_, Text.Length(_) - 1), type text}}) else ToTable,
    #"Added Custom" = Table.AddColumn(#"Trimmed Text2", "Custom", each fnCreateQuestionComponentTable([Column1], c_QuestionComponentDelimiter)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", c_QuestionComponentList())
in
    #"Expanded Custom"

fnCreateQuestionComponentTable

(MyText as text, Separator as text) => 
let
    Result = Table.FromRows({Text.Split(MyText, Separator)}, c_QuestionComponentList())
in
    Result

Anónimo

Por último, la tabla principal para hacer realidad toda la magia:

let
    Source = <Your Table Here>,
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Id", "ChecklistData", "GUID"}),  // Only need your ID columns and your parsing column    #"Added Answers" = Table.AddColumn(#"Removed Other Columns", "Answers", each fnSplitQuestions([ChecklistData])),
    #"Expanded Answers" = Table.ExpandTableColumn(#"Removed Columns", "Answers", c_QuestionComponentList(), c_QuestionComponentList())  // Expand the columns
in
    #"Expanded Answers"

Desde aquí, debería poder vincular esto a su tabla original para ver los resultados.

@Anónimo,

¿Podría compartir datos de muestra de su consulta para que podamos comprender mejor el problema?

Saludos,
lidia

Anónimo

Aquí hay una cadena de ejemplo para ser analizada en un solo campo. La siguiente cadena es para un solo registro que se encuentra en una tabla de registros de inspección. Esto proviene de un campo «ChecklistData», los otros campos son metadatos que son bastante fáciles por sí solos.

1,MIC85Q,Phycology Lab,3,4,N/A,N/A,-0.05,##2,RN687Q,Oven in volatiles prep,N/A,N/A,22,24,-3.95,##3,ORG160Q,Volatiles Prep Lab,3,4,N/A,N/A,0.3,##4,EN541Q,Waters Lab Freezer,2,4,N/A,N/A,0.6,##5,EN723Q,Waters Lab Cold Room,6,8,N/A,N/A,-1.2,##6,EN757Q,Byth St Cold Room,2.2,2.3,N/A,N/A,-1.45,##7,EN758Q,Byth St Cold Room,1.0,1.3,N/A,N/A,-1.65,##8,EN759Q,Inorgs Lab Reagent Fridge,5,6,N/A,N/A,-0.95,##9,EN382Q,BOD Incubator,34,35,N/A,N/A,-0.1,##10,EN536Q,On the wall in BOD area,3,5,N/A,N/A,0.4,##11,EN724Q,Sample fridge in Metals water prep room,2,4,N/A,N/A,-1.4,##12,EN879Q,Solids Oven,N/A,N/A,33,34,3.25,##13,EN920Q,Solids Oven,N/A,N/A,24,25,1.9,##14,EN660Q,40° Moisture oven,N/A,N/A,40,42,-1.45,##15,EN549Q,Dust Oven,N/A,N/A,34,N/A,-7.6,##16,EN287Q,Solids Oven,N/A,N/A,45,46,0,##17,EN804Q,O&G Oven,N/A,N/A,45,N/A,-3.9,##18,EN919Q,Moisture oven in TCLP room,N/A,N/A,36,36,0.95,##19,EN361Q,On the wall in the TCLP lab (read IN screen),2,3,N/A,N/A,-0.85,##20,EN380Q,Fridge in dioxins soil prep room,3,6,N/A,N/A,0.3,##21,EN381Q,Freezer in dioxins soil prep room,4,5,N/A,N/A,0.05,##22,EN188Q,Left oven in dioxins soil prep room,N/A,N/A,55,N/A,-4.5,##23,EN407Q,Right oven in dioxins soil prep room,N/A,N/A,45,N/A,3.3,##24,EN289Q,Oven in dioxins water prep room,N/A,N/A,46,45,-0.35,##25,EN606Q,Oven (1st on right) in outside prep area,N/A,N/A,64,54,0.5,##26,ORG118Q,Column Leaching room,4,5,N/A,N/A,0.25,##27,MIC21Q,Micro Fridge (In Food / Pharma Room),2,3,N/A,N/A,1.9,##28,MIC23Q,Micro Air Temp (directly inside 3rd door),1,2,N/A,N/A,0.2,##29,MIC86Q,Micro Fridge (1st),2,3,N/A,N/A,0.45,##30,MIC25Q,Micro Refrigerated incubator,N/A,N/A,N/A,55,-1.3,##31,MIC42Q,Micro Incubator along wall (1st),N/A,N/A,N/A,45,-1.65,##32,MIC26Q,Micro Incubator&nbsp; along wall (2nd),N/A,N/A,N/A,53,0,##33,MIC27Q,Micro Incubator along wall (3rd),N/A,N/A,N/A,67,0.4,##34,MIC87Q,Micro Incubator along wall (4th),N/A,N/A,N/A,75,0.1,##35,MIC109Q,Micro Incubator along wall (5th),N/A,N/A,N/A,65,0,##36,MIC83Q,Micro Incubator under Fume Hood,N/A,N/A,N/A,67,0.3,##37,MIC61Q,Micro incubator under bench (left),N/A,N/A,N/A,46,-1.2,##38,MIC64Q,Micro incubator under bench (right),N/A,N/A,N/A,56,0.1,##39,EN682Q,Chest Freezer in Sample Receipt,34,44,N/A,N/A,0.7,##40,EME033Q,Glass Door Fridge in Sample Receipt,2,3,N/A,N/A,1.1,##41,EN647Q,3 Door Freezer in Sample Receipt,4,5,N/A,N/A,-0.4,##42,EN735Q,Upright Freezer in Sample Receipt,4,5,N/A,N/A,-0.05,##

Esto debe dividirse en 42 preguntas, que estarán separadas por ##

Cada pregunta se compone de componentes separados por comas en:

Número de pregunta
Termia#
Ubicación
Cálculo mínimo
Calc. máx.
Cálculo de temperatura térmica
Cálculo de la temperatura de la pantalla del horno
Lectura Variable de calibración

El método actual es tomar el campo «ChecklistData» y dividirlo por ## en 42 tablas. 1 tabla para cada pregunta. Esto se hace primero en una tabla de etapas que contiene los registros GUID y cada pregunta como una columna. Cada una de las 42 tablas usa la tabla de preparación como fuente, elimina las 41 preguntas que no necesita y luego cada una realiza un conjunto de columnas divididas por delimitador usando las operaciones «más a la izquierda» y «más a la derecha». Esto es para evitar problemas con las comas que se encuentran en el texto de la ubicación. Cada columna recibe un nombre coherente establecido en las 42 tablas.

Finalmente, se crea una tabla que agrega las 42 tablas nuevamente a una tabla final. Debido a la nomenclatura consistente, esencialmente analizamos la cadena de texto dos veces y desviábamos los datos.