TXT file with multiple headers and data accross columns?

Power Query
1

Attached an extract from a giant txt file,
we want to get proper file for reporting the data?
how to get the eigenaar field repeated in a column
how to get the VRD.PLA. field repeated in a column
how to append the data from the right columns

thanks in advance
Roger

TXT file for power query.TXT (5.5 KB)

TXT file for power Query
TXT file for power Query657Ă—693 234 KB

2

Hi @Roger,

See if this works for you. Note that you might have to adjust the split positions a little…
To add columnt for the “eigenaar” and “VRD.PLA.” I used conditional logic like so:

if Text.StartsWith([Column1], "0EIGENAAR :") then 
    Text.AfterDelimiter([Column1], "0EIGENAAR : ") else 
    null
)

I’ve added 2 support queries containing column names as a list. Below the end result. You will need to do a bit of clean up like removing spaces, setting appropriate data types but this should get you started.

image
image1680Ă—476 48.2 KB

Here’s your sample file.
eDNA - split and combine.pbix (12.4 KB)

I hope this is helpful

3

Hi @Roger

See attached PBIX where I have worked through the request. I just saw that @Melissa had slipped in while I was working on this :slightly_smiling_face: She is the master, so all the below may be academic

Eigenaar and VRD

  • apply an index column named RowNumber at the beginning.

  • Filter just Eigenaar and VRD

  • Create a Row From And Row To column then build a list in each row of all numbers from Row From and Row To

  • Expand List to create a table and then use this to merge the original table and apply the references to each row.

Block of Data on Right

  • Split the data colum with * as delimiter and delete one of the cols and save query.

  • Do this again saving the other column

  • Append previous 2 queries into one and then split the column of data

The only bit I can’t quite crack is the final splitting, probably beacuse I’m using a UK keyboard and the data is Dutch so dec places are commas.

1 Like
4

Just ran down @Melissa solution.
Why didn’t I think of Fill Down! Much cleaner and simpler

1 Like
5

Melissa,
great solution when I look at the print screen, you’re a genious!!
But to use the pbix ? have I go to the advanced editor to make changes?

kind regards
Roger

6

Hi @Roger,

Glad to help.
You’ll need to update the file location, then the query will restore.

7

Melissa
I have some issues with the file maybe because I send you only extract of the original file
the pbix crashes or results in an empty table
I upload the original file could you take a look at it please

thanks in advance

Roger
RFP01.D12100062150.P0000.TXT (862.3 KB)

8

pete
thanks for the tips in solving this problem

Roger

9

Melissa,

could you please check if your solution pbix file works on the complete Txt file? for the “voorraadplaats 3WA” the data on the right side is not picked up

thanks in advance
roger

Knipsel voor melissa
Knipsel voor melissa1077Ă—113 32.9 KB

10

Melissa

could you test the pbix you made for this problem? I attached the complete txt file and some is nog working correctly
thanks in advance

Roger

11

Hi @Roger,

Give this a go.
Just add your full file path to the FileLocation Parameter list and select it.
eDNA - split and combine.pbix (12.7 KB)

12

Melissa
I changed the file location and the query works fine ( i get 11463 records) but when i click close and apply power BI shuts down. If i choose copy entire table i just get the first 999 rows

any idea what is going wrong?
thanks in advance

Roger

13

Have you tried updating or re-installing Power BI Desktop?

In the application bar at the bottom of your screen, change: “Column profiling on top 1000 rows” into the entire data set.

14

thanks for all the support, works well with the latest version of power BI

Roger