Creating a unstructured table

[PBIDF]

Hi guys,

New to PBI, and in need of some assitance (assuming it's possible).

I need to create an upload file with a fixed format via PowerBI.

The unstructured format is below:

3 tags, TXT1 which is at the top, below it follows the table columns. Under that then the DATA tag and below it, goes the records in the table. At the very end a closing tag of EOT.

<TXT1>
The columns of a table go here
<DATA>
Table data goes here
<EOT>

Normally I would use SSRS for this, but sadly that is not accessable for me.

Thanks in advance

Regards,

[ToddChitt]

Not sure I understand the format. Are there commas separating the columns and values? It simply a CSV file? Can you post a picture of the file contents from Notepad? Feel free to obfuscate sensitive data as needed.

[PBIDF]

it was a excel file. It looks like this. Should of included it apologies.

[ToddChitt]

Ah, we can work with this in Power Query. 

Load it using an Excel connector, but delete any and all Applied Steps other than Source and Navigation, stuff like Changed Type and especially Promoted Headers. (Is the data maybe CSV? It will still work.)

Now apply steps in order:

1. Remove top rows, number to remove: 1
2. Promote Headers
3. Filter column <ID> to remove values of <Data> and <EOT>
4. Change the Data Type of <ID> column to INT.
5. Rename column headers for readability if you want.