Best way to Parse text within Power Query

[Anonymous]

A collegue of mine has written a Power Apps solution for the business that asks a bunch of questions and stores the results in sharepoint.  The data captured by this Power Apps solution however is all stored in a single field. 

The format used separate each question is by '##' (no quotes) and then uses comma's to separate out each part of the answer details.  This contains values such as 'Question Number', 'Question asked', 'Comments', and each answer box (i.e. Max Temp, Min Temp, Adjustment value).  Each question follows the same format, simply the 'Question asked' text may differ.  The comments and answer boxes are always the same data type and name.  Apart from the 'Comments', no commas are possible in the stored text.

The solution I wrote to get the job done works but feels..... slow.  I'm curious if anyone has come up with a more optimised method.  My method was as follows:

Take the whole text, row by row, and break it into 1 table per each Question.

For each question (table), do another parse operation to break each answer into its own column.  I use a combination of First Comma and Last comma splits to avoid any extra comma's in the Comments section.  There is no data wrapping occuring.  Each split out column is given a corresponding name to its data (i.e. Comments, Max Temp, Min Temp).

Lastly I merge all of the question tables back together to give a complete list of answers unpivoted.

This gives me the data I need to do all of the Power BI goodness, but the solution is all hard coded.  If someone requests a new question to be added, everything needs to be replicated by hand.  If something changes in the question structure, there is a lot of manual editing.  This also makes the Power Query UI pause for a long time as it recalculates after every change.  My kingdom for a loop and 3 dimensional array!

Interested in other solutions!

[ImkeF · Accepted solution]

Hi @Anonymous,

please check this method:

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  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"

Replace the expression of step "MyFunction" with your specific function code for the text separation.

This method performs the "loop" simply in a table with one row per question.

Cheers,

Imke Feldmann

www.TheBIccountant.com -- How to integrate M-code into your solution  -- Check out more PBI- learning resources here

[Anonymous · Accepted solution]

@ImkeFThis worked as brilliantly as your reputation would suggest.  Here is the finalised solution in case this is helpful to others:

We modified the format slightly to correct certain issues.  Firstly we wrapped all of the values in ' to allow for commas to exist in the text.  Lastly the format was incorrectly placing the delimiter at the end of the string, which i have had removed.

To make this a repeatable platform I firstly created some constants, in their own folder. They were:
c_QuestionComponentList   (Edit this for your own format)

() =>
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  (This includes the wrap characters)

let
    Source = "','"
in
    Source

c_QuestionDelimiter

let
    Source = "##"
in
    Source

switch_Wrap  (Lets me turn off and on whether a wrap is being used.  0 for No, 1 for Yes)

let
    Source = 1
in
    Source

[Anonymous · Accepted solution]

Now for the functions we will use:

fnSplitQuestions

(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

[v-yuezhe-msft]

@Anonymous,

Could you please share sample data of your query so that we can better understand the problem?

Regards,
Lydia

[Anonymous]

Here is an example string to be parsed held in a single field.  This below string is for a single record found in a table of Inspection records.  This comes from a field "ChecklistData", the other fields are meta data which is easy enough on their own.

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  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,##

This needs to be broken down into 42 questions, which will be separated by ##

Each Question is made up of components separated by commas into:

• Question Number
• Therm#
• Location
• Min Calc
• Max Calc
• Therm temp Calc
• Oven Display Temp Calc
• Reading Calibration variable

The current method is to take the "ChecklistData" field, and split it by ## into 42 tables. 1 Table for each question.  This is done in a staging table first which contains the records GUID and each question as a column.  Each of the 42 tables uses the staging table as a source, deletes the 41 questions it doesn't need and then each performs a set of split column by delimiter using "Left most" and "right most" operations.  This is to avoid any issues with commas being found in the location text.  Each column is given a consistant name set across the 42 tables.

Finally a table is created that appends all 42 tables back into a final table.  Due to the consistant naming, we have essentially parsed the text string twice and unpivoted the data.

[ImkeF · Accepted solution]

Hi @Anonymous,

please check this method:

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  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"

Replace the expression of step "MyFunction" with your specific function code for the text separation.

This method performs the "loop" simply in a table with one row per question.

Cheers,

Imke Feldmann

www.TheBIccountant.com -- How to integrate M-code into your solution  -- Check out more PBI- learning resources here

[Anonymous · Accepted solution]

@ImkeFThis worked as brilliantly as your reputation would suggest.  Here is the finalised solution in case this is helpful to others:

We modified the format slightly to correct certain issues.  Firstly we wrapped all of the values in ' to allow for commas to exist in the text.  Lastly the format was incorrectly placing the delimiter at the end of the string, which i have had removed.

To make this a repeatable platform I firstly created some constants, in their own folder. They were:
c_QuestionComponentList   (Edit this for your own format)

() =>
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  (This includes the wrap characters)

let
    Source = "','"
in
    Source

c_QuestionDelimiter

let
    Source = "##"
in
    Source

switch_Wrap  (Lets me turn off and on whether a wrap is being used.  0 for No, 1 for Yes)

let
    Source = 1
in
    Source

[Anonymous · Accepted solution]

Now for the functions we will use:

fnSplitQuestions

(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

[Anonymous · Accepted solution]

Lastly the main table to make all the magic happen:

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"

From here you should be able to link this back to your original table for the results.

[Anonymous]

I like the way that looks.  Hopefully i can score some time on Monday to get to test that.  You are a magician at M so i'm very glad to see your name come up.