Parsing multiple lines text as columns

[sevenhills]

Hi

I would like to parse multiple lines text and get them as text. 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("1VTBjtowEP2VEadFIpQAu61APbC7HJAKW7GoF7QHE0+Iu4mdeuzdpl/fSQKF1mgvPVS1lCix/Z7nvXnJdtuBt8Zyfr+4m32CzXo+2yznq03nqbftNLfVegMrQSKHx9KKqplrMPeGlN5PII6GIA0hvcuMt3D1IRoPDhNSVN3pCXEcRmOzAT42j1TzgtKAIslAG3JW5dP2rV2TmKsXtASD/jUUezApaJUYpzSeCr31pTWlMhoe12GVt7jnE+paXxGfCUqrjAVn4JtXDqRwOIX4esDsYb0l74LZEr7DiHdWBFeCGJqjZZjs9sBlqFlAYlGwKiZ9g2mnZB/ujHZWKC1VIhyXTBPIUEjm+Opt1QNC9cNbpB67wCovMElFxkr2pAfL2QMDM7VTrpaUcVll1W8hzf2LsMh+5Ue7LhnT2HLJlQtH1xbE0WhybMfBn2m7Mo7en6+w3gscbROGUTzkAJ12hhuXRjeyUr40eu4wVUmmBGckAaqK0pmCzsX+Cu2D5cwudCa4T6Hsmyi+gURY5pH7NqpTdlE5JfK8Ak8Y1hKfACy4zlKdeI7DDskBpikmjuBVuSzEJtxxpb3xHD2fplxDI2nIOeFph9QNMQsijxJSi/yRiKLMkRqQs1WNN971Q9BnDgxqVzexPlMkrv60jrF3qsDQroXDgjiUXrsJjM8WQvo/x9/8OUb/+r/xG/vsxSgJTjzX3hbGtmcdzPq/UxU2echNfvoJ", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}})
in
    #"Changed Type"

I would like to convert as columns. 

Where
(a) Treatment Type is begin of a group of lines

    Say, "NRT Nasal Spray", "Bupropion SR", ....

(b) Dosing is a group of lines after "Dosing: "

     Say, "Dosing: 1-2 doses/hour (8-40 doses/day);

             one dose = one spray in each nostril; each spray delivers 0.5 mg of nicotine"

          "Dosing: Begin 1-2 weeks prior to quit date; 150 mg

             po q AM x 3 days (as tolerated), then increase to 150 mg

             po bid. Contraindications: head injury, seizures, eating

             disorders, MAO inhibitor therapy."

(c) Items Count is straight forward from a single line.

           4, 2 

TIA

[ThxAlot · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("1VTBjtowEP2VEadFIpQAu61APbC7HJAKW7GoF7QHE0+Iu4mdeuzdpl/fSQKF1mgvPVS1lCix/Z7nvXnJdtuBt8Zyfr+4m32CzXo+2yznq03nqbftNLfVegMrQSKHx9KKqplrMPeGlN5PII6GIA0hvcuMt3D1IRoPDhNSVN3pCXEcRmOzAT42j1TzgtKAIslAG3JW5dP2rV2TmKsXtASD/jUUezApaJUYpzSeCr31pTWlMhoe12GVt7jnE+paXxGfCUqrjAVn4JtXDqRwOIX4esDsYb0l74LZEr7DiHdWBFeCGJqjZZjs9sBlqFlAYlGwKiZ9g2mnZB/ujHZWKC1VIhyXTBPIUEjm+Opt1QNC9cNbpB67wCovMElFxkr2pAfL2QMDM7VTrpaUcVll1W8hzf2LsMh+5Ue7LhnT2HLJlQtH1xbE0WhybMfBn2m7Mo7en6+w3gscbROGUTzkAJ12hhuXRjeyUr40eu4wVUmmBGckAaqK0pmCzsX+Cu2D5cwudCa4T6Hsmyi+gURY5pH7NqpTdlE5JfK8Ak8Y1hKfACy4zlKdeI7DDskBpikmjuBVuSzEJtxxpb3xHD2fplxDI2nIOeFph9QNMQsijxJSi/yRiKLMkRqQs1WNN971Q9BnDgxqVzexPlMkrv60jrF3qsDQroXDgjiUXrsJjM8WQvo/x9/8OUb/+r/xG/vsxSgJTjzX3hbGtmcdzPq/UxU2echNfvoJ", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),

    Trimmed = Table.TransformColumns(#"Changed Type", {"Column1", each let txt = Text.Trim(_) in if txt = "" then null else txt}),
    #"Grouped per TRMT" = Table.Group(
        Trimmed,
        "Column1",
        {"TRMT", each let ln = List.Select(Table.Group(_, "Column1", {"Line", each Text.Combine([Column1], " ")}, 0, (x,y) => Byte.From(y is null))[Line], each _<>""), lns = List.Transform(List.Range(ln,1,List.Count(ln)-2), each Text.Split(_, " Dosing: ")), cnt = List.Count(lns) in Table.AddColumn(Table.FromRows(lns), "Cnt", each cnt)},
        0,
        (x,y) => Byte.From(Text.Contains(y,"MEDICAL TREATMENT",Comparer.OrdinalIgnoreCase) ?? false)
    ),
    #"Added Index" = Table.AddIndexColumn(Table.RemoveColumns(#"Grouped per TRMT", "Column1"), "Index", 1, 1, Int64.Type),
    #"Expanded TRMT" = Table.ExpandTableColumn(#"Added Index", "TRMT", {"Column1", "Column2", "Cnt"}, {"Medicine", "Dosing", "Cnt"})
in
    #"Expanded TRMT"

[sevenhills]

Thanks to both @ThxAlot  and @m_dekorte and marked both of your replies as solution. 

[sevenhills]

Both solutions provided with respect to my original post worked. Thanks to @m_dekorte , @ThxAlot  

Now, I went back to the complete PBIX, which is bunch of files in a folder and lot more combinations. I reached to this point 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("7VdZb+M2EP4rg2AfNoAPHXa2TdAH+QjqRXw0VlsEQR5okbbYlUWvSCVxf32HlGwplu0Num23G5iADZvkDOf6viHv788sy4aXo//zLfhiRoJAQJdJSRQXMdyylUiU2dAjil2CZdc//npTdyzHxbkJWeCcffZQuz+r/9PDaIVjY+L5g/7Ih8Hoemx2T9BqFisY9NCqiouV4Wnr220jOiJL/PNRSLYKa/B7whehAv+g6JQ9X8LQSHqUJhgwPNFxa9AnUoHjqhC8RxanrAZdrtZloyMSwy8pV3lEbafp2k0MaMto+7LX/t2kD+Nr8Mcdr9sdF1JdviAJU4qhLXdMFieS4JOEFUuAkjUeCCucaOLv7Y5CsqnXLgEcKwsoXzHoiKcIVY6a3lbATG1VFqfjNj3Kp4/S5Qz36b1PjKHuzM+xCnFG5RW3SgRNA4XiU8W1tVl+liLFbObHNDNx14j362Vvy6Zpd0iiEk4XrOR1NpaL5vJG/4j455RTiHkgFI8xDd+qhof93qDr3YB/2/f8IVZzkc/RrQ8jIkkE01VC1oWunpA8XmAm6w5QrFjZDEWawPsf6i0rn0Cfz6+qp4uYmQ3wk/kptV7gMTAShBALiWGLrrJ/2RplEX9kiQSr0cbogZhvY1YY2kkxfyvNF9PbqpUdtsATtK06gZiShAudefisQUARBFdgty3UXrV3hbvAG8IzuDqNEt4TiaIRS1CMntcAqyhGB4KEEfQKlR7RNOO0AV0Rq4TwmPLAUBxWT8gIRR1/pMm6BpLxP1PEcw2jgF7u0US5FAnFmNRg6I1RMOQzrrRLWNFktW4UgTFfluXs5PwruNY5ce2Ja09ce7CGzddvaCyeFm1IypizQ0cmQPu4aA/gNfHYdfdyQ4I5K11lK636h/IKssweHRn1OXXb0UWx3VndOBSxIZM5fmKWIq/KdRBygkEOQK6XKyWWMqMYeNkqxgl2ikEcEmTHqtsXdfsCgm2ydEleIXdxxUkUrSGVrGqLXQigw5rBdZ9BEp4xBB6bzxmWETxxFVZlA+RZHqcixbpI53O0wbjkIDvjNFbSeWH9QLGlRGbGAsSShVdic+Ld6X4Jvb7vDW6mpfRzuUW8ceMCeXSU8ahtXbqWl5HJOKLQIUgRQbbxndNqN6wMjB0eRTkmsjXXrJVsy7nd3bHqK7jdPXH7idtP3H6whgtgTginuSnv2pa1Aa0vFFJgL2WbtR+3SyOxYYM88BnukbxQ3YxhKrm+A4JjG1KvwLy1Uzxv8LlsHRWGHOYtqwTzDvZZShCaId6gD4M8h/n1IZjbzuthbjmI8TcG8/LWXZgfBXlZcB/IvzuIH83k1zyV3W/9UH6h3XsUGFFFPulr0VIk2Vmy8dKh7/JCB9UbnVO6Nfn4UFdLTTqakbEeDb651K/qIJWSUXO8fksj7gw9NfZXxoa8DIMnJhMiVUDmCuvYtgyNN17JEa+/SlqvvEpaX7hI7u9lpbVKL9tqzDtSe8eHN9iRLo4KQ96RXLfUkaZEpQm6LZBE1THR4xfPU0f67zrSy6vV/obUMpeTwy0Jo/U/7Ep7uNA6QoUj9qzg0TDOU34zXQjddChThEfybxFh618iwgNv6mxf7pAuHiwOsjZexoxRRs8eHv4C", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Record Number Row", each if Text.Contains([Column1],"EHR Tobacco Cessation Report") then [Index] else null, Int64.Type),
    #"Added Conditional Column" = Table.AddColumn(#"Added Custom", "Record Number Link Back", each if Text.Contains([Column1], "   Page: 1") then [Record Number Row] else null, Int64.Type),
    #"Filled Down" = Table.FillDown(#"Added Conditional Column",{"Record Number Row", "Record Number Link Back"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Index] > [Record Number Row] + 14)),
    #"Added Conditional Column1" = Table.AddColumn(#"Filtered Rows", "Medical or Payment Section", each if Text.Contains([Column1], "                             MEDICAL TREATMENT") then "Medical Treatment Section" else if Text.Contains([Column1], "                             PAYMENT DETAILS") then "Payment Section" else null, type text),
    #"Filled Down1" = Table.FillDown(#"Added Conditional Column1",{"Medical or Payment Section"}),
    #"Filtered Rows1" = Table.SelectRows(#"Filled Down1", each ([Medical or Payment Section] = "Medical Treatment Section")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows1",{"Record Number Row"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Record Number Link Back", "Medical or Payment Section"}, {{"allRows", each _, type table [Column1=nullable text, Index=number, Record Number Link Back=number, Medical or Payment Section=text]}})
in
    #"Grouped Rows"

(a) Has "Items Count: 0" which means no treatment type and dosing lines.
(b) Has optional multiple lines "Treatment Notes:". These are optional set of lines.

                             MEDICAL TREATMENT

 
Items Count: 0

Treatment Notes: Next visit we will go in details.
                 Patient followup in the next 14 days.

                             MEDICAL TREATMENT

NRT Nasal Spray
     Dosing: 3 doses/hour (8-40 doses/day);
             one dose = one spray in each nostril; each spray delivers 0.5 mg of nicotine
			 Avoid taking more doses.
NRT Oral Inhaler
     Dosing: 6-16 cartridges/day; initially use
             1 cartridge q 1-2 hours (best effects with
             continuous puffing for 20 minutes)
 
Items Count: 2 

Treatment Notes: Plan is discussed with the patient.
                 Patient followup in the next 10 days.

Appreciate if you can let me know to resolve. TIA.

[m_dekorte]

Hi @sevenhills,

It's unclear how you'd like to handle optional notes. I've now added them as separate column, you can filter them out or remove them if not nessicary.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("7VdZb+M2EP4rg2AfNoAPHXa2TdAH+QjqRXw0VlsEQR5okbbYlUWvSCVxf32HlGwplu0Num23G5iADZvkDOf6viHv788sy4aXo//zLfhiRoJAQJdJSRQXMdyylUiU2dAjil2CZdc//npTdyzHxbkJWeCcffZQuz+r/9PDaIVjY+L5g/7Ih8Hoemx2T9BqFisY9NCqiouV4Wnr220jOiJL/PNRSLYKa/B7whehAv+g6JQ9X8LQSHqUJhgwPNFxa9AnUoHjqhC8RxanrAZdrtZloyMSwy8pV3lEbafp2k0MaMto+7LX/t2kD+Nr8Mcdr9sdF1JdviAJU4qhLXdMFieS4JOEFUuAkjUeCCucaOLv7Y5CsqnXLgEcKwsoXzHoiKcIVY6a3lbATG1VFqfjNj3Kp4/S5Qz36b1PjKHuzM+xCnFG5RW3SgRNA4XiU8W1tVl+liLFbObHNDNx14j362Vvy6Zpd0iiEk4XrOR1NpaL5vJG/4j455RTiHkgFI8xDd+qhof93qDr3YB/2/f8IVZzkc/RrQ8jIkkE01VC1oWunpA8XmAm6w5QrFjZDEWawPsf6i0rn0Cfz6+qp4uYmQ3wk/kptV7gMTAShBALiWGLrrJ/2RplEX9kiQSr0cbogZhvY1YY2kkxfyvNF9PbqpUdtsATtK06gZiShAudefisQUARBFdgty3UXrV3hbvAG8IzuDqNEt4TiaIRS1CMntcAqyhGB4KEEfQKlR7RNOO0AV0Rq4TwmPLAUBxWT8gIRR1/pMm6BpLxP1PEcw2jgF7u0US5FAnFmNRg6I1RMOQzrrRLWNFktW4UgTFfluXs5PwruNY5ce2Ja09ce7CGzddvaCyeFm1IypizQ0cmQPu4aA/gNfHYdfdyQ4I5K11lK636h/IKssweHRn1OXXb0UWx3VndOBSxIZM5fmKWIq/KdRBygkEOQK6XKyWWMqMYeNkqxgl2ikEcEmTHqtsXdfsCgm2ydEleIXdxxUkUrSGVrGqLXQigw5rBdZ9BEp4xBB6bzxmWETxxFVZlA+RZHqcixbpI53O0wbjkIDvjNFbSeWH9QLGlRGbGAsSShVdic+Ld6X4Jvb7vDW6mpfRzuUW8ceMCeXSU8ahtXbqWl5HJOKLQIUgRQbbxndNqN6wMjB0eRTkmsjXXrJVsy7nd3bHqK7jdPXH7idtP3H6whgtgTginuSnv2pa1Aa0vFFJgL2WbtR+3SyOxYYM88BnukbxQ3YxhKrm+A4JjG1KvwLy1Uzxv8LlsHRWGHOYtqwTzDvZZShCaId6gD4M8h/n1IZjbzuthbjmI8TcG8/LWXZgfBXlZcB/IvzuIH83k1zyV3W/9UH6h3XsUGFFFPulr0VIk2Vmy8dKh7/JCB9UbnVO6Nfn4UFdLTTqakbEeDb651K/qIJWSUXO8fksj7gw9NfZXxoa8DIMnJhMiVUDmCuvYtgyNN17JEa+/SlqvvEpaX7hI7u9lpbVKL9tqzDtSe8eHN9iRLo4KQ96RXLfUkaZEpQm6LZBE1THR4xfPU0f67zrSy6vV/obUMpeTwy0Jo/U/7Ep7uNA6QoUj9qzg0TDOU34zXQjddChThEfybxFh618iwgNv6mxf7pAuHiwOsjZexoxRRs8eHv4C", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Record Number Row", each if Text.Contains([Column1],"EHR Tobacco Cessation Report") then [Index] else null, Int64.Type),
    #"Added Conditional Column" = Table.AddColumn(#"Added Custom", "Record Number Link Back", each if Text.Contains([Column1], "Page: 1") then [Record Number Row] else null, Int64.Type),
    #"Filled Down" = Table.FillDown(#"Added Conditional Column",{"Record Number Row", "Record Number Link Back"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Index] > [Record Number Row] + 14)),
    #"Added Conditional Column1" = Table.AddColumn(#"Filtered Rows", "Medical or Payment Section", each if Text.Contains([Column1], "MEDICAL TREATMENT") then "Medical Treatment Section" else if Text.Contains([Column1], "PAYMENT DETAILS") then "Payment Section" else null, type text),
    #"Filled Down1" = Table.FillDown(#"Added Conditional Column1",{"Medical or Payment Section"}),
    #"Filtered Rows1" = Table.SelectRows(#"Filled Down1", each ([Medical or Payment Section] = "Medical Treatment Section")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows1",{"Record Number Row"}),
    FilterRows = Table.SelectRows(#"Removed Columns", each ( [Column1] <> "" and [Column1] <> " " and [Column1] <> "                             MEDICAL TREATMENT")),
    getTreatment = Table.FillDown( Table.AddColumn(FilterRows, "Treatment Type", each if Text.StartsWith([Column1], "Treatment" ) then [Treatment Type= "", i = [Index]] else if Text.Start( [Column1], 1 ) <> " " and not Text.StartsWith([Column1], "Items" ) then [Treatment Type= [Column1], i = [Index]] else null), {"Treatment Type"}),
    #"Expanded Treatment Type" = Table.ExpandRecordColumn(getTreatment, "Treatment Type", {"Treatment Type", "i"}, {"Treatment Type", "i"}),
    getDosing = Table.AddColumn(#"Expanded Treatment Type", "Dosing", each if Text.Start( [Column1], 4 ) = "    " and Text.Length([Treatment Type]) >1 then [Column1] else null),
    getCount = Table.FillUp( Table.AddColumn(getDosing, "Items Count", each if Text.StartsWith([Column1], "Items" ) then Text.Select( [Column1], {"0".."9"}) else null), {"Items Count"}),
    Custom1 = Table.AddColumn(getCount, "Notes", each if Text.StartsWith( [Column1], "Treatment" ) or Text.Start( [Column1], 4 ) = "    " and Text.Length([Treatment Type]) <1 then [Column1] else null),
    RemoveCols = Table.RemoveColumns(Custom1,{"Column1", "Index"}),
    GroupRows = Table.Group(RemoveCols, {"i", "Dosing"}, {{"t", each Table.FromRows( {{List.First([Record Number Link Back]), List.First([Treatment Type]), Text.Combine( List.Transform([Dosing], Text.Trim), " "), List.First([Items Count]), Text.Combine( List.Transform([Notes], Text.Trim), " ")}}, type table [Record Number Link Back= number, Treatment Type=nullable text, Dosing=nullable text, Items Count=nullable text, Notes=text] )}}, GroupKind.Local, (x, y)=> Number.From( x[i] <> y[i] and y[Dosing] = null) ),
    t = Table.Combine( GroupRows[t] )
in
    t

with this result

I hope this is helpful

[sevenhills]

Perfect! your idea is good i.e., adding as columns. 

The code output shows "Items Count column" is wrong for the optional rows and last record. (last 2 rows in your pic). Please could you check.

[m_dekorte · Accepted solution]

Hi @sevenhills 

Right, I missed that. Give this a go instead.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("7VdZb+M2EP4rg2AfNoAPHXa2TdAH+QjqRXw0VlsEQR5okbbYlUWvSCVxf32HlGwplu0Num23G5iADZvkDOf6viHv788sy4aXo//zLfhiRoJAQJdJSRQXMdyylUiU2dAjil2CZdc//npTdyzHxbkJWeCcffZQuz+r/9PDaIVjY+L5g/7Ih8Hoemx2T9BqFisY9NCqiouV4Wnr220jOiJL/PNRSLYKa/B7whehAv+g6JQ9X8LQSHqUJhgwPNFxa9AnUoHjqhC8RxanrAZdrtZloyMSwy8pV3lEbafp2k0MaMto+7LX/t2kD+Nr8Mcdr9sdF1JdviAJU4qhLXdMFieS4JOEFUuAkjUeCCucaOLv7Y5CsqnXLgEcKwsoXzHoiKcIVY6a3lbATG1VFqfjNj3Kp4/S5Qz36b1PjKHuzM+xCnFG5RW3SgRNA4XiU8W1tVl+liLFbObHNDNx14j362Vvy6Zpd0iiEk4XrOR1NpaL5vJG/4j455RTiHkgFI8xDd+qhof93qDr3YB/2/f8IVZzkc/RrQ8jIkkE01VC1oWunpA8XmAm6w5QrFjZDEWawPsf6i0rn0Cfz6+qp4uYmQ3wk/kptV7gMTAShBALiWGLrrJ/2RplEX9kiQSr0cbogZhvY1YY2kkxfyvNF9PbqpUdtsATtK06gZiShAudefisQUARBFdgty3UXrV3hbvAG8IzuDqNEt4TiaIRS1CMntcAqyhGB4KEEfQKlR7RNOO0AV0Rq4TwmPLAUBxWT8gIRR1/pMm6BpLxP1PEcw2jgF7u0US5FAnFmNRg6I1RMOQzrrRLWNFktW4UgTFfluXs5PwruNY5ce2Ja09ce7CGzddvaCyeFm1IypizQ0cmQPu4aA/gNfHYdfdyQ4I5K11lK636h/IKssweHRn1OXXb0UWx3VndOBSxIZM5fmKWIq/KdRBygkEOQK6XKyWWMqMYeNkqxgl2ikEcEmTHqtsXdfsCgm2ydEleIXdxxUkUrSGVrGqLXQigw5rBdZ9BEp4xBB6bzxmWETxxFVZlA+RZHqcixbpI53O0wbjkIDvjNFbSeWH9QLGlRGbGAsSShVdic+Ld6X4Jvb7vDW6mpfRzuUW8ceMCeXSU8ahtXbqWl5HJOKLQIUgRQbbxndNqN6wMjB0eRTkmsjXXrJVsy7nd3bHqK7jdPXH7idtP3H6whgtgTginuSnv2pa1Aa0vFFJgL2WbtR+3SyOxYYM88BnukbxQ3YxhKrm+A4JjG1KvwLy1Uzxv8LlsHRWGHOYtqwTzDvZZShCaId6gD4M8h/n1IZjbzuthbjmI8TcG8/LWXZgfBXlZcB/IvzuIH83k1zyV3W/9UH6h3XsUGFFFPulr0VIk2Vmy8dKh7/JCB9UbnVO6Nfn4UFdLTTqakbEeDb651K/qIJWSUXO8fksj7gw9NfZXxoa8DIMnJhMiVUDmCuvYtgyNN17JEa+/SlqvvEpaX7hI7u9lpbVKL9tqzDtSe8eHN9iRLo4KQ96RXLfUkaZEpQm6LZBE1THR4xfPU0f67zrSy6vV/obUMpeTwy0Jo/U/7Ep7uNA6QoUj9qzg0TDOU34zXQjddChThEfybxFh618iwgNv6mxf7pAuHiwOsjZexoxRRs8eHv4C", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Record Number Row", each if Text.Contains([Column1],"EHR Tobacco Cessation Report") then [Index] else null, Int64.Type),
    #"Added Conditional Column" = Table.AddColumn(#"Added Custom", "Record Number Link Back", each if Text.Contains([Column1], "Page: 1") then [Record Number Row] else null, Int64.Type),
    #"Filled Down" = Table.FillDown(#"Added Conditional Column",{"Record Number Row", "Record Number Link Back"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Index] > [Record Number Row] + 14)),
    #"Added Conditional Column1" = Table.AddColumn(#"Filtered Rows", "Medical or Payment Section", each if Text.Contains([Column1], "MEDICAL TREATMENT") then "Medical Treatment Section" else if Text.Contains([Column1], "PAYMENT DETAILS") then "Payment Section" else null, type text),
    #"Filled Down1" = Table.FillDown(#"Added Conditional Column1",{"Medical or Payment Section"}),
    #"Filtered Rows1" = Table.SelectRows(#"Filled Down1", each ([Medical or Payment Section] = "Medical Treatment Section")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows1",{"Record Number Row"}),
    FilterRows = Table.SelectRows(#"Removed Columns", each ( [Column1] <> "" and [Column1] <> " " and [Column1] <> "                             MEDICAL TREATMENT")),
    getTreatment = Table.FillDown( Table.AddColumn(FilterRows, "Treatment Type", each if Text.StartsWith([Column1], "Treatment" ) then [Treatment Type= "", i = [Index]] else if Text.Start( [Column1], 1 ) <> " " and not Text.StartsWith([Column1], "Items" ) then [Treatment Type= [Column1], i = [Index]] else null), {"Treatment Type"}),
    #"Expanded Treatment Type" = Table.ExpandRecordColumn(getTreatment, "Treatment Type", {"Treatment Type", "i"}, {"Treatment Type", "i"}),
    getDosing = Table.AddColumn(#"Expanded Treatment Type", "Dosing", each if Text.Start( [Column1], 4 ) = "    " and Text.Length([Treatment Type]) >1 then [Column1] else null),
    getCount = Table.AddColumn(getDosing, "Items Count", each if Text.StartsWith([Column1], "Items" ) then Text.Select( [Column1], {"0".."9"}) else null),
    #"Grouped Rows" = Table.Combine( Table.Group(getCount, {"Record Number Link Back"}, {{"t", each Table.FillDown( Table.FillUp(_, {"Items Count"}), {"Items Count"}), type table [Column1=nullable text, Index=number, Record Number Link Back=number, Medical or Payment Section=text, Treatment Type=text, i=number, Dosing=nullable text, Items Count=nullable text]}})[t] ),
    Custom1 = Table.AddColumn(#"Grouped Rows", "Notes", each if Text.StartsWith( [Column1], "Treatment" ) or Text.Start( [Column1], 4 ) = "    " and Text.Length([Treatment Type]) <1 then [Column1] else null),
    RemoveCols = Table.RemoveColumns(Custom1,{"Column1", "Index"}),
    GroupRows = Table.Group(RemoveCols, {"i", "Dosing"}, {{"t", each Table.FromRows( {{List.First([Record Number Link Back]), List.First([Treatment Type]), Text.Combine( List.Transform([Dosing], Text.Trim), " "), List.First(List.RemoveNulls( [Items Count])), Text.Combine( List.Transform([Notes], Text.Trim), " ")}}, type table [Record Number Link Back= number, Treatment Type=nullable text, Dosing=nullable text, Items Count=nullable text, Notes=text] )}}, GroupKind.Local, (x, y)=> Number.From( x[i] <> y[i] and y[Dosing] = null) ),
    t = Table.Combine( GroupRows[t] )
in
    t

with this result

I hope this is helpful

[sevenhills]

Thank you very much @m_dekorte . Appreciate it. 

I took your logic and adapted to multiple multi line sections and nested sections.

Additional details: It worked when I ran the same logic for over 100 files (i.e., in my real time project, and each data file has over thousands of rows). Like linking all data files from SP, and make it available as tables such as Patient Info, Tobacco Info, Medical Treatment, Payment & Visit details ... Performance wise it is kind of slow but given the limitation of the tech stack and data format, it is working due to too many grouping and nesting. All good! 

[ThxAlot · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("1VTBjtowEP2VEadFIpQAu61APbC7HJAKW7GoF7QHE0+Iu4mdeuzdpl/fSQKF1mgvPVS1lCix/Z7nvXnJdtuBt8Zyfr+4m32CzXo+2yznq03nqbftNLfVegMrQSKHx9KKqplrMPeGlN5PII6GIA0hvcuMt3D1IRoPDhNSVN3pCXEcRmOzAT42j1TzgtKAIslAG3JW5dP2rV2TmKsXtASD/jUUezApaJUYpzSeCr31pTWlMhoe12GVt7jnE+paXxGfCUqrjAVn4JtXDqRwOIX4esDsYb0l74LZEr7DiHdWBFeCGJqjZZjs9sBlqFlAYlGwKiZ9g2mnZB/ujHZWKC1VIhyXTBPIUEjm+Opt1QNC9cNbpB67wCovMElFxkr2pAfL2QMDM7VTrpaUcVll1W8hzf2LsMh+5Ue7LhnT2HLJlQtH1xbE0WhybMfBn2m7Mo7en6+w3gscbROGUTzkAJ12hhuXRjeyUr40eu4wVUmmBGckAaqK0pmCzsX+Cu2D5cwudCa4T6Hsmyi+gURY5pH7NqpTdlE5JfK8Ak8Y1hKfACy4zlKdeI7DDskBpikmjuBVuSzEJtxxpb3xHD2fplxDI2nIOeFph9QNMQsijxJSi/yRiKLMkRqQs1WNN971Q9BnDgxqVzexPlMkrv60jrF3qsDQroXDgjiUXrsJjM8WQvo/x9/8OUb/+r/xG/vsxSgJTjzX3hbGtmcdzPq/UxU2echNfvoJ", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),

    Trimmed = Table.TransformColumns(#"Changed Type", {"Column1", each let txt = Text.Trim(_) in if txt = "" then null else txt}),
    #"Grouped per TRMT" = Table.Group(
        Trimmed,
        "Column1",
        {"TRMT", each let ln = List.Select(Table.Group(_, "Column1", {"Line", each Text.Combine([Column1], " ")}, 0, (x,y) => Byte.From(y is null))[Line], each _<>""), lns = List.Transform(List.Range(ln,1,List.Count(ln)-2), each Text.Split(_, " Dosing: ")), cnt = List.Count(lns) in Table.AddColumn(Table.FromRows(lns), "Cnt", each cnt)},
        0,
        (x,y) => Byte.From(Text.Contains(y,"MEDICAL TREATMENT",Comparer.OrdinalIgnoreCase) ?? false)
    ),
    #"Added Index" = Table.AddIndexColumn(Table.RemoveColumns(#"Grouped per TRMT", "Column1"), "Index", 1, 1, Int64.Type),
    #"Expanded TRMT" = Table.ExpandTableColumn(#"Added Index", "TRMT", {"Column1", "Column2", "Cnt"}, {"Medicine", "Dosing", "Cnt"})
in
    #"Expanded TRMT"

[sevenhills]

Thank you @ThxAlot . Let me try your logic on my bigger power bi files processing and get you back... 

[m_dekorte]

Hi @sevenhills 

See if this works for you.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("1VTBjtowEP2VEadFIpQAu61APbC7HJAKW7GoF7QHE0+Iu4mdeuzdpl/fSQKF1mgvPVS1lCix/Z7nvXnJdtuBt8Zyfr+4m32CzXo+2yznq03nqbftNLfVegMrQSKHx9KKqplrMPeGlN5PII6GIA0hvcuMt3D1IRoPDhNSVN3pCXEcRmOzAT42j1TzgtKAIslAG3JW5dP2rV2TmKsXtASD/jUUezApaJUYpzSeCr31pTWlMhoe12GVt7jnE+paXxGfCUqrjAVn4JtXDqRwOIX4esDsYb0l74LZEr7DiHdWBFeCGJqjZZjs9sBlqFlAYlGwKiZ9g2mnZB/ujHZWKC1VIhyXTBPIUEjm+Opt1QNC9cNbpB67wCovMElFxkr2pAfL2QMDM7VTrpaUcVll1W8hzf2LsMh+5Ue7LhnT2HLJlQtH1xbE0WhybMfBn2m7Mo7en6+w3gscbROGUTzkAJ12hhuXRjeyUr40eu4wVUmmBGckAaqK0pmCzsX+Cu2D5cwudCa4T6Hsmyi+gURY5pH7NqpTdlE5JfK8Ak8Y1hKfACy4zlKdeI7DDskBpikmjuBVuSzEJtxxpb3xHD2fplxDI2nIOeFph9QNMQsijxJSi/yRiKLMkRqQs1WNN971Q9BnDgxqVzexPlMkrv60jrF3qsDQroXDgjiUXrsJjM8WQvo/x9/8OUb/+r/xG/vsxSgJTjzX3hbGtmcdzPq/UxU2echNfvoJ", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    AddIndex = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
    GroupID = Table.AddColumn(AddIndex, "Custom", each if Text.Contains( [Column1], "MEDICAL TREATMENT" ) then [Index] else null ),
    FillDownGroupID = Table.FillDown(GroupID,{"Custom"}),
    FilterRows = Table.SelectRows(FillDownGroupID, each ( [Column1] <> "" and [Column1] <> " " and [Column1] <> "                             MEDICAL TREATMENT")),
    getTreatment = Table.AddColumn(FilterRows, "Treatment Type", each if Text.Start( [Column1], 1 ) <> " " and not Text.StartsWith([Column1], "Items" ) then [Column1] else null),
    getDosing = Table.AddColumn(getTreatment, "Dosing", each if Text.Start( [Column1], 4 ) = "    " then [Column1] else null),
    getCount = Table.AddColumn(getDosing, "Items Count", each if Text.StartsWith([Column1], "Items" ) then Text.Select( [Column1], {"0".."9"}) else null),
    RemoveCols = Table.RemoveColumns(getCount,{"Column1", "Index"}),
    GroupRows = Table.Group(RemoveCols, {"Custom"}, {{"t", each Table.FromColumns( List.Transform( List.Transform( List.Skip( Table.ToColumns(_), 1), List.RemoveNulls), (x)=> {Text.Combine(x, "#(cr,lf)")}),type table [Treatment Type=nullable text, Dosing=nullable text, Items Count=nullable text]), type table [Treatment Type=nullable text, Dosing=nullable text, Items Count=nullable text]}}),
    ExpandTables = Table.Combine( GroupRows[t] )
in
    ExpandTables

Here's the output

Or maybe

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("1VTBjtowEP2VEadFIpQAu61APbC7HJAKW7GoF7QHE0+Iu4mdeuzdpl/fSQKF1mgvPVS1lCix/Z7nvXnJdtuBt8Zyfr+4m32CzXo+2yznq03nqbftNLfVegMrQSKHx9KKqplrMPeGlN5PII6GIA0hvcuMt3D1IRoPDhNSVN3pCXEcRmOzAT42j1TzgtKAIslAG3JW5dP2rV2TmKsXtASD/jUUezApaJUYpzSeCr31pTWlMhoe12GVt7jnE+paXxGfCUqrjAVn4JtXDqRwOIX4esDsYb0l74LZEr7DiHdWBFeCGJqjZZjs9sBlqFlAYlGwKiZ9g2mnZB/ujHZWKC1VIhyXTBPIUEjm+Opt1QNC9cNbpB67wCovMElFxkr2pAfL2QMDM7VTrpaUcVll1W8hzf2LsMh+5Ue7LhnT2HLJlQtH1xbE0WhybMfBn2m7Mo7en6+w3gscbROGUTzkAJ12hhuXRjeyUr40eu4wVUmmBGckAaqK0pmCzsX+Cu2D5cwudCa4T6Hsmyi+gURY5pH7NqpTdlE5JfK8Ak8Y1hKfACy4zlKdeI7DDskBpikmjuBVuSzEJtxxpb3xHD2fplxDI2nIOeFph9QNMQsijxJSi/yRiKLMkRqQs1WNN971Q9BnDgxqVzexPlMkrv60jrF3qsDQroXDgjiUXrsJjM8WQvo/x9/8OUb/+r/xG/vsxSgJTjzX3hbGtmcdzPq/UxU2echNfvoJ", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    AddIndex = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
    GroupID = Table.AddColumn(AddIndex, "Custom", each if Text.Contains( [Column1], "MEDICAL TREATMENT" ) then [Index] else null ),
    FillDownGroupID = Table.FillDown(GroupID,{"Custom"}),
    FilterRows = Table.SelectRows(FillDownGroupID, each ( [Column1] <> "" and [Column1] <> " " and [Column1] <> "                             MEDICAL TREATMENT")),
    getTreatment = Table.FillDown( Table.AddColumn(FilterRows, "Treatment Type", each if Text.Start( [Column1], 1 ) <> " " and not Text.StartsWith([Column1], "Items" ) then [Column1] else null), {"Treatment Type"}),
    getDosing = Table.AddColumn(getTreatment, "Dosing", each if Text.Start( [Column1], 4 ) = "    " then [Column1] else null),
    getCount = Table.FillUp( Table.AddColumn(getDosing, "Items Count", each if Text.StartsWith([Column1], "Items" ) then Text.Select( [Column1], {"0".."9"}) else null), {"Items Count"}),
    RemoveCols = Table.RemoveColumns(getCount,{"Column1", "Index"}),
    GroupRows = Table.Group(RemoveCols, {"Treatment Type", "Dosing"}, {{"t", each Table.FromRows( {{List.First([Treatment Type]), Text.Combine( List.Transform([Dosing], Text.Trim), " "), List.First([Items Count])}}), type table [Treatment Type=nullable text, Dosing=nullable text, Items Count=nullable text]}}, GroupKind.Local, (x, y)=> Number.From( x[Treatment Type] <> y[Treatment Type] and y[Dosing] = null) ),
    t = Table.Combine( GroupRows[t] )
in
    t

With this result

Amend to your needs.

I hope this is helpful

[sevenhills]

Thank you @m_dekorte . Second solution is the one I am interested.

Let me try your logic on my bigger power bi files processing and get you back... 

[sevenhills]

This is part of some bigger PBI file. I have to use only Power BI to parse.

I can get this in PowerShell as below: 

$Text = "MEDICAL TREATMENT

NRT Nasal Spray
     Dosing: 1-2 doses/hour (8-40 doses/day);
             one dose = one spray in each nostril; each spray delivers 0.5 mg of nicotine

Bupropion SR
     Dosing: Begin 1-2 weeks prior to quit date; 150 mg
             po q AM x 3 days (as tolerated), then increase to 150 mg
             po bid. Contraindications: head injury, seizures, eating
             disorders, MAO inhibitor therapy.
 
Varenicline
     Dosing: Begin 1 week prior to quit date;
             days 1-3: 0.5 mg po q AM; days 4-7: 0.5 mg po bid;
             weeks 2-12: 1 mg po bid
             Monitor for neuropsychiatric symptoms.
 

NRT Oral Inhaler
     Dosing: 6-16 cartridges/day; initially use
             1 cartridge q 1-2 hours (best effects with
             continuous puffing for 20 minutes)
             Issued free samples for trying out.
             Patient to contact in 2 weeks times.
 

Items Count: 4 


                             MEDICAL TREATMENT

NRT Nasal Spray
     Dosing: 3 doses/hour (8-40 doses/day);
             one dose = one spray in each nostril; each spray delivers 0.5 mg of nicotine
             Avoid taking more doses.

NRT Oral Inhaler
     Dosing: 6-16 cartridges/day; initially use
             1 cartridge q 1-2 hours (best effects with
             continuous puffing for 20 minutes)

Items Count: 2 
"
cls
$rows = $Text -split "`n"

$table = New-Object System.Data.DataTable "dt1"
$c1 = $table.Columns.Add("Treatment") 
$c2 = $table.Columns.Add("Items Count") 
$c3 = $table.Columns.Add("Dosing") 

$t1 = $table.Clone() 
$t2 = $table.Clone() 

$TreatmentType   = ""
$Dosing          = ""
$ItemsCount      = ""

foreach ($row in $rows) {
    # $row

    # First row of section
    if ($row.Contains("MEDICAL TREATMENT")   ) { 
        $t1 = $table.Clone()
        continue; 
    }
    # last row of the group, update the current set Items Count!
    elseif ($row.Contains("Items Count: ") ) {
        $t1.Rows.Add($TreatmentType, $null, $Dosing) | OUT-NULL 
        $ItemsCount = $row.Split(':')[1].Trim()
        $t1.Rows | foreach { $_["Items Count"] = $ItemsCount } 
        $t2.Merge($t1)

        # rest
        $t1 = $table.Clone()
        $TreatmentType   = ""
        $Dosing          = ""
        $ItemsCount      = ""
        continue;
    }

    if (-not $row.StartsWith("  ") -and -not $row.Contains(":") -and $row.Length -gt 10) {
        if ($Dosing -ne "") {
            #$TreatmentType, $Dosing
            $t1.Rows.Add($TreatmentType, $null, $Dosing) | OUT-NULL 
        }
        
        $TreatmentType = $row.Trim().Replace("`n", "")
    }
    elseif ($row.Contains("Dosing:") -and $row.Length -gt 10) {
        $Dosing = ""
        $Dosing = $row.Replace("Dosing: ", "").Trim().Replace("`n", "")
    }
    elseif ($row.Trim() -ne "") {
        $Dosing += $row.Trim().Replace("`n", "") +"; "
    }
}


# Print the table
 $t2 | Format-Table

Output of Powershell: