Transforming rows to columns from a report output

Hi sevenhills ,

I've gone about it a slightly different way from you i.e. I've built up all the columns from the original table, then filled them around the [medicine] and [dosage] columns (as these are the only ones that can be distinct within a group).

I think the addition of the STOP row could be slicker, maybe using a record function, but couldn't get my head in gear for it.

The only difference I got to your example output was on poor old Joleen's special cream, where your output stated a dosage of 1, but the data had N/A. Probably easy enough for you to add a step into my code to fix this if required.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("3ZXdb9owEMD/lRPaIzDbwAtvoem2ToJGLdWkoT6Y5CCW/BE5piP//YwDIyCyrSuapuUhSu4u9+Vf7haLDiEUTq8k51bxtIIHLIx1QRZzh2MgtDeNHnqMMLaz42svo53n7qKTcCdQO7iLvYSQIJtx5fUTizrjXbjJeQbzoIiyzGJZeks26MItL51/cjlEL6g36E2Fq2qv+0zG8NGajc5gJY2xUOzFTRtRujHMqs0aNcyNrnW1gY+VWlE4YTTcOVTlOMinmIlUaJ/ivJKojYQRIWoddLEpQ3GsX5fyVL9OjdVCr4H7VGIuZAUUHF9KdMBXDi2sjMmOkY8R7leSb3fPwEbnMWh/dCkGDVG0WOcOyN4k3EIJcOP74Stm0FJofWCU9D7gsj4wSsYDEk2D6b3MYMIl16k38tc70t8f2kRICZGqve80dDg60SVcZOMalKZubhyXEG/woPvhMdwIYVenjDYo+2wkou7CFxsa1soZG1yDs38XtAlPnRUK4scrkNZIPK+MFVvtM1fpudOLmR/EZ46+VtZh+julN7OiTZAGbwKJ/RcgfULFrc2NyCC1yNVJQ2fvI2h2NCoKD1DNDfI0h6X5hhKUeUHlm9AyWUa/mCz0zZOF/WSykNa50vhqD8Tw6pOFNYBI+EZ2IcFd9/wO03+6wW4VWn/UPq1WGiLfNnjMsSi4zV6Dw2GuXPrlrzNX/Llt//buGh4JI8wTlrx+d1HSvrtoO2O15vk7", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Line = _t]),
    addSeqID = Table.AddColumn(Source, "seqID", each if Text.Contains([Line], "Total Due:") then "STOP" else if Text.Contains([Line], "Page:") then Text.BeforeDelimiter([Line], " ") else null, type text),
    addReportName = Table.AddColumn(addSeqID, "reportName", each if Text.Contains([Line], "Total Due:") then "STOP" else if [seqID] <> null then Text.BetweenDelimiters([Line], [seqID], "Date:") else null, type text),
    addDate = Table.AddColumn(addReportName, "date", each if Text.Contains([Line], "Total Due:") then "STOP" else if [seqID] <> null then Text.BetweenDelimiters([Line], "Date:", "Page:") else null),
    addPage = Table.AddColumn(addDate, "page", each if Text.Contains([Line], "Total Due:") then "STOP" else if [seqID] <> null then Text.AfterDelimiter([Line], "Page:") else null, type text),
    addPatientID = Table.AddColumn(addPage, "patientID", each if Text.Contains([Line], "Total Due:") then "STOP" else if Text.Contains([Line], "Patient ID:") then Text.AfterDelimiter([Line], "Patient ID:") else null, type text),
    addName = Table.AddColumn(addPatientID, "name", each if Text.Contains([Line], "Total Due:") then "STOP" else if Text.Contains([Line], "Name:") then Text.AfterDelimiter([Line], "Name:") else null, type text),
    addPharmacy = Table.AddColumn(addName, "pharmacy", each if Text.Contains([Line], "Total Due:") then "STOP" else if Text.Contains([Line], "Pharmacy:") then Text.AfterDelimiter([Line], "Pharmacy:") else null, type text),
    addMedicine = Table.AddColumn(addPharmacy, "medicine", each if Text.Contains([Line], "Total Due:") or Text.Contains([Line], "Usage:") then "STOP" else if Text.Contains([Line], "Medicine:") then Text.AfterDelimiter([Line], "Medicine:") else null, type text),
    addDosage = Table.AddColumn(addMedicine, "dosage", each if Text.Contains([Line], "Total Due:") or Text.Contains([Line], "Usage:") then "STOP" else if Text.Contains([Line], "Dosage:") then Text.AfterDelimiter([Line], "Dosage:") else null),
    addItemsCount = Table.AddColumn(addDosage, "itemCount", each if Text.Contains([Line], "Total Due:") then "STOP" else if Text.Contains([Line], "Items Count:") then Text.AfterDelimiter([Line], "Items Count:") else null),
    addPrescDate = Table.AddColumn(addItemsCount, "prescriptionDate", each if Text.Contains([Line], "Total Due:") then "STOP" else if Text.Contains([Line], "Prescription Date:") then Text.AfterDelimiter([Line], "Prescription Date:") else null),
    addBillAmount = Table.AddColumn(addPrescDate, "billAmount", each if Text.Contains([Line], "Total Due:") then "STOP" else if Text.Contains([Line], "Bill Amount:") then Text.AfterDelimiter([Line], "Bill Amount:") else null),
    trimTextTypeCols = Table.TransformColumns(addBillAmount,{{"seqID", Text.Trim, type text}, {"reportName", Text.Trim, type text}, {"date", Text.Trim, type text}, {"page", Text.Trim, type text}, {"patientID", Text.Trim, type text}, {"name", Text.Trim, type text}, {"pharmacy", Text.Trim, type text}, {"medicine", Text.Trim, type text}}),
    remLineCol = Table.RemoveColumns(trimTextTypeCols,{"Line"}),
    fillDownToMedicine = Table.FillDown(remLineCol,{"seqID", "reportName", "date", "page", "patientID", "name", "pharmacy", "medicine"}),
    fillUpFromDosage = Table.FillUp(fillDownToMedicine,{"dosage", "itemCount", "prescriptionDate", "billAmount"}),
    filterMedStopNull = Table.SelectRows(fillUpFromDosage, each ([medicine] <> null and [medicine] <> "STOP")),
    tableDistinct = Table.Distinct(filterMedStopNull)
in
    tableDistinct

Pete

try change at those points in the way you see in the pictures

regarding your questions about using the table.group function it takes some time to make a detailed explanation.

You can find somethink here

Hi BA_Pete 

Final code:
Raw Data

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("7ZbdT9swEMD/lRPaY2G2Q2HrW0rZxqRCBZ0mDfFwJNfGwrEjx2Xkv8dN2pFWhM90YhN5iJK7i+/Dv9z5/HyLMQ6r1yhBm2JUwCllxrpSNkBHPWB8exiebgsmxNwOp17Gty4651sjdJK0g6OBlzBWyo4x9fq+JR1jBw4SjGFcKsI4tpTn3lIEHTjE3Pknl0B4TXpG3lS6olp1EUkPvloz0zFMlDEWsoW4biNz14PjYjYlDWOjK11l4H1FVmZOGg1HjtK8V8qHFMtIah/iuFCkjYIuY+m01A1MXiYndqpUflSvQ2O11FNAH8oApSqAg8NLRQ5w4sjCxJj4zvOdh5OJwpv5M4juug++073PBy+9aDlNHLCFSXkrU4ADXw+fsYCGRKsN42z7C11WG8ZZL2DhsDQ9UTH0UaGOvJG/PrCdxab1pVIQptXqcw3f7a7oRijjXgVKXTc2DhUMZrTU/VmxvDEmWqeM1yj7bhSR7sBPWxaskTMRtMHZ2wWtj5GzMoXBWQuk1QJPCmPljfaRp9H6ovdGvhSvLfSrsI6ip6Rej4rXQQpeBZL4L0D6RilamxgZQ2QJ05WCHn8MoV7RMMs8QBU3hFECl+Y3KUjNNaW+CA2dpftIZ+Gv7izigc7CGvtK7asFELutdxZRA2KEM9WBEc2r52eYfukEO0zJ+q32YTXSEPqywVlCWYY2fg4Oy75y3y/fTl/x+3bzt2fX7h1hTHjCRs+fXZw1zy7ezNj67Oq2TlhQI+wAXVK8I9YSYptlNniE2U+vZ3bvAWb3ns7sXuvMfq6PSYyuOjCc5Vdw+DZH5PtZq9X4FlTtt374+reo2szBq8Xz8eZ/gJV+uL/xU2LwolPi8quLWw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Line = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Line", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"Index", "Line"}),
    #"Added Conditional Column" = Table.AddColumn(#"Reordered Columns", "Section", each if Text.Contains([Line], "Page:") then "Page Row" else if Text.Contains([Line], "Patient ID") 
        then "Header Patient" else if Text.Contains([Line], "Prescription Items:") then "Items" else if Text.Contains([Line], "Items Count:") then "Footer" else if Text.Contains([Line], "Total Due:") then "End Patient" else null),
    #"Sorted Rows" = Table.Sort(#"Added Conditional Column",{{"Index", Order.Ascending}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Sorted Rows", "Section", "Section - Begin"),
    #"Filtered Rows" = Table.SelectRows(#"Duplicated Column", each [Line] <> null and [Line] <> ""),
    #"Filled Down" = Table.FillDown(#"Filtered Rows",{"Section"}),
    #"Added Suffix" = Table.TransformColumns(#"Filled Down", {{"Section - Begin", each _ & " - Begin", type text}}),

    #"Added Conditional Column1" = Table.AddColumn(#"Added Suffix", "Seq Number", each if [#"Section - Begin"] = "Page Row - Begin" then Text.Trim(Text.Start([Line], 5)) else null, type nullable text),
    #"Filled Down1" = Table.FillDown(#"Added Conditional Column1",{"Seq Number"}),
    #"Added Conditional Column2" = Table.AddColumn(#"Filled Down1", "Patient ID", 
        each if [#"Section - Begin"] = "Header Patient - Begin" then Text.Trim(Text.AfterDelimiter([Line], "Patient ID: ")) else null  , type nullable text),
    #"Filled Down2" = Table.FillDown(#"Added Conditional Column2",{"Patient ID"}),
    #"Replaced Value" = Table.ReplaceValue(#"Filled Down2", each [Patient ID], each if [#"Section - Begin"] = "Page Row - Begin" then null else [Patient ID],Replacer.ReplaceValue,{"Patient ID"}),
    #"Grouped Rows" = Table.Group(#"Replaced Value", {"Section", "Patient ID"}, {{"Rows Count", each Table.RowCount(_), Int64.Type}, {"First Row", each List.Min([Index]), type number}, {"End Row", each List.Max([Index]), type number}, {"TempTable100", each _, type table [Index=number, Line=nullable text, Section=text, #"Section - Begin"=text, Seq Number=nullable text, Patient ID=nullable text]}})
in
    #"Grouped Rows"

Data Prep - Page Row

let
    Source = #"Raw Data",
    #"Filtered Rows" = Table.SelectRows(Source, each ([Section] = "Page Row")),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"TempTable100"}),
    #"Expanded TempTable100" = Table.ExpandTableColumn(#"Removed Other Columns", "TempTable100", {"Index", "Line", "Section", "Section - Begin", "Seq Number", "Patient ID"}, {"Index", "Line", "Section", "Section - Begin", "Seq Number", "Patient ID"}),
    #"Split Column by Positions" = Table.SplitColumn(#"Expanded TempTable100", "Line", Splitter.SplitTextByPositions({0, 18, 44, 58, 64}), {"Line.1", "Line.2", "Line.3", "Line.4", "Line.5"}),
    #"Replaced Value" = Table.ReplaceValue(#"Split Column by Positions","     Date: ","",Replacer.ReplaceText,{"Line.2"}),
    #"Removed Columns" = Table.RemoveColumns(#"Replaced Value",{"Line.4", "Section", "Section - Begin", "Patient ID", "Line.1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Line.5", "Page"}, {"Line.3", "Date"}, {"Line.2", "Title"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Seq Number", "Index", "Title", "Date", "Page"})
in
    #"Reordered Columns"

Data Prep - HF Rows

et
    Source = #"Raw Data",
    #"Filtered Rows" = Table.SelectRows(Source, each ([Section] <> "Page Row" and [Section] <> "Items" )),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"TempTable100"}),
    #"Expanded TempTable100" = Table.ExpandTableColumn(#"Removed Other Columns", "TempTable100", {"Index", "Line", "Section", "Section - Begin", "Seq Number", "Patient ID"}, {"Index", "Line", "Section", "Section - Begin", "Seq Number", "Patient ID"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Expanded TempTable100", "Line", Splitter.SplitTextByDelimiter(": ", QuoteStyle.Csv), {"Line.1", "Line.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Line.1", type text}, {"Line.2", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Index", "Section", "Section - Begin", "Seq Number"}),
    #"Trimmed Text" = Table.TransformColumns(#"Removed Columns",{{"Line.1", Text.Trim, type text}, {"Line.2", Text.Trim, type text}}),
    #"Filtered Rows1" = Table.SelectRows(#"Trimmed Text", each ([Line.1] <> "Patient ID")),
    #"Removed Duplicates" = Table.Distinct(#"Filtered Rows1"),
    #"Pivoted Column" = Table.Pivot(#"Removed Duplicates", List.Distinct(#"Removed Duplicates"[Line.1]), "Line.1", "Line.2")
in
    #"Pivoted Column"

Data Prep - Items

let
    Source = #"Raw Data",
    #"Filtered Rows" = Table.SelectRows(Source, each ([Section] = "Items" )),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"TempTable100"}),
    #"Expanded TempTable100" = Table.ExpandTableColumn(#"Removed Other Columns", "TempTable100", {"Index", "Line", "Section", "Section - Begin", "Seq Number", "Patient ID"}, {"Index", "Line", "Section", "Section - Begin", "Seq Number", "Patient ID"}),
    #"Filtered Rows1" = Table.SelectRows(#"Expanded TempTable100", each ([Line] <> "Prescription Items:") and not Text.StartsWith([Line], "Usage")),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Filtered Rows1", "Line", Splitter.SplitTextByDelimiter(": ", QuoteStyle.Csv), {"Line.1", "Line.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Line.1", type text}, {"Line.2", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Section", "Section - Begin"}),
    #"Trimmed Text" = Table.TransformColumns(#"Removed Columns",{{"Line.1", Text.Trim, type text}, {"Line.2", Text.Trim, type text}}),
    #"Added Conditional Column" = Table.AddColumn(#"Trimmed Text", "G1", 
          each 
          if [Line.1] = "Medicine" then "G1_L_" & Number.ToText([Index])
          else if [Line.1] = "Items Count" then "G2_" & [Patient ID]
          else null),
    #"Sorted Rows" = Table.Sort(#"Added Conditional Column",{{"Index", Order.Ascending}}),
    #"Filled Down" = Table.FillDown(#"Sorted Rows",{"G1"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Filled Down",{"Index"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"[Line.1]), "Line.1", "Line.2"),
    #"Replaced Value" = Table.ReplaceValue(#"Pivoted Column","N/A","1.0",Replacer.ReplaceText,{"Dosage"})
in
    #"Replaced Value"

Data

let
    Source = Table.NestedJoin(#"Data Prep - HF Rows", {"Patient ID"}, #"Data Prep - Items", {"Patient ID"}, "Raw Data - Items", JoinKind.LeftOuter),
    #"Expanded Raw Data - Items" = Table.ExpandTableColumn(Source, "Raw Data - Items", {"Seq Number", "Medicine", "Dosage"}, {"Seq Number", "Medicine", "Dosage"}),
    #"Merged Queries" = Table.NestedJoin(#"Expanded Raw Data - Items", {"Seq Number"}, #"Data Prep - Page Row", {"Seq Number"}, "Raw Data - Page Row", JoinKind.LeftOuter),
    #"Expanded Raw Data - Page Row" = Table.ExpandTableColumn(#"Merged Queries", "Raw Data - Page Row", {"Title", "Date", "Page"}, {"Title", "Date", "Page"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded Raw Data - Page Row",{"Patient ID", "Name", "Items Count", "Prescription Date", "Medicine", "Dosage", "Old Balance", "Bill Amount", "Bill Paid", "Total Due", "Seq Number", "Title", "Date", "Page", "Address", "Pharmacy", "Pharmacist"})
in
    #"Reordered Columns"

I will be happy to hear comments/updates/suggestions.

Converted as functions, used to process each file in the folder.