Converting file data into columns - power query

[sevenhills]

Hi all,

I am combining multiple files, which are semistructured. I am providing sample data which I need to convert into columns. Any help is appreciated. The data looks like below:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("zZhNc6IwHMa/SoZeq+YNEG9dtbM9bN90xoP1wNSsMqXgYDz0229CBMIUbA3pjhyQB0KeHyE8+Y/LpYOca+eZ7dKMg7vJCDw/zvu/0/0u4mHcn7BdmPF3lvA+guiFA3073jQJORsBDDHpQdpDSFxBwQhCveljuGHyPEj/ggA6q+ulg4VtfkAq/3nEY9HXIs3ewM1+H20SaQ1mW8Z43pYWN7nioKAc5RZS8SiMAYLKwCva+uJgnK4ZqJ4GfNqeDikPAbhLOMsS0Yt4rA95HtIB8gby6ZTARBMElsIdQF8TiGoCo1zkMEMB89n9m5v0xzpMoPt7uj/R/XVM7FcwgYDpfbEBtQelBvmuOpGrbiKHQVDQTMXbA9N3lm1Y8vrRPAq+mlo3CP56AmB2SPKhgRhX4wTxUBeBLpAmCNEFUhzI6BX9SQsO3eAEh45b58CKAxtxzA9MdYNq1u0cNeum8SBGHAu2PnLU3HQO0n6lJqjioGbjsT0cDVrfS+2pT3C4isM14rjNoga3uvC+x3F8L54RxyzkRTdan7Qm6FkcfhG06Lxwu2dsLSfJ4k0RIVKtGWLW6IJqAsPWZoGnOIKCCMtMGctMGW+jeJ2xZN/MQtz8Z+x5t7gtU2of1IlZ3fANYZuZ0oXDZqZ04bCZKV04bGZKFw6bmdKFw2amdOEoMwVfSKbgMlMI/H8FM9YKZoJKAnxGyUxIeRv9umgmbtnau6SymfiXVDeT4SUVzkROzQc50x+S1zRONy11808XzhReRuFMzRZb64UzNVtsrRfO1GyxtV44U7PF1nrhTM0WW+uFMy3/oaDnpduPLXJ0WBLJTLnqg+n9BFz1ndXqHw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"File Row Number" = _t, #"Row Details" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"File Row Number", Int64.Type}, {"Row Details", type text}})
in
    #"Changed Type"

Dates are dynamic and each file will have different dates.

Output format expected:

   Data columns:

• Report ID
• Report Date Time
• Report Title
• Hospital
• Code
• Department
• Quota
• Internal
• Date Range
• Day
• Value
• Optional: Page Details

TIA

[lbendlin]

I would suggest you ... run for the seven hills ...  (Sorry, could not resist)

Since the data is in fixed width you can split each line (row) by position as needed.  It will be a giant effort to set that up but it's not that complex.