Fixed Width Import Issue

[dal211]

Hi all,

  I'm kind of a beginner in PowerBI, but I have a pretty large ASCII dataset that I'm trying to delimit into proper columns. For each variable, I have the text position mapping in order to delimit this ASCII file as shown below:

And with the "Import Fixed Width" field, I plug these values into PowerBI import as shown below:

 

Because I need to import the text positions in ascending order as required by PowerBI, I technically have as an example 

6,7,14,18,20,21,28,28,29,31,36,39...

This causes an error within PowerBI as the numbers 28 are repeated twice (...28, 28....). But I still want to include the variables "Time of Birth" - positions 21-28 and "DOB WK" - 25-28 in the delimiting process. This duplication of numbers happens frequently throughout my dataset.

If I want to make PowerBI accept my series of numbers, I have to choose between:

One series that ONLY includes "Time of Birth" positions 21-28

6,7,14,18,20,21,28,28,29,31,36,39

OR

One series that ONLY includes "DOB WK" positions 25-28

6,7,14,18,20,25,28,28,29,31,36,39

I read some things on the forum about table.splitcolumn, but I'm an utter novice in PowerBI.

Can someone give me some direction? 

Many Thanks,

David

 

 

 

[Eduardo_Suela]

Maybe your problem is that the file is ragged (= not all lines have the same length)

[HotChilli]

What does the data look like in Power Query after import?

Also, it would probably help if you post a small sample (not a picture) here of the original data (just enough to show the issues).

If it's sensitive data then just mock it up.

[dal211]

Thanks.

This is the first line of what the raw ASCII data looks like: it's not properly delimited and has no headers:

A 0 201301 99993GU 999922 2 34105JA GU 999 9 1107 4 08 X1 3 32 0507 4 086 00 1 1 031 1509 253 N00000000 N NNNNNNN NXXN00 2 222 NN NNNNNNN 2 2 NNNNN NNNNNN 2 2 2 11X 221 1NNNNNN1N22 104885 1 M 03 201240 40082 3317093 NNNNNNN NNNNNNNNNNNN22 2 2 2 2 UYU 2888888888888888111136619 11111111111111111111 1111111 111111111111 111111 1111111111111111111111111111 1 11 1 111111 11 1 111 1 1 1 11 11 1 1 1

This is the full data dictionary, listing all the positions of the variables delimiting the ASCII file above:

https://docs.google.com/spreadsheets/d/1Y4YiryitBV9fuNdnwk8ko5k-6HYqP6G7ClhsnAQC5FU/edit?usp=sharing

If I show the imported data into PowerBI, it hides the issues because I cannot add all the variables due to overlapping positions, let me know how I should show you this 

[HotChilli]

I don't understand what that data is.  It's delimited and it doesn't seem to match the data dictionary at all.

Maybe you can link some sample data in original form with rows that exhibit the problem.  I need to see enough rows that show the problem.

----

If you can post the sample data then take a screenshot of what powerbi does when you import the same file.  You can describe it or annotate it to describe the problem and show me the desired result.

[HotChilli]

I'm not seeing any further clarification.  Powerbi won't let you use this 

6,7,14,18,20,28,28,29,31,36,39

so you have to use something that'll get your data into power query and worry about the correct columns once it's in there.

What happens when you use this?

6,7,14,18,20,28,29,31,36,39

[dal211]

That series of numbers works, but then I'm excluding a ton of variables that have some position overlaps with other variables, which I'm not sure how to fix after I import

What would you recommend for me to add those variables after import, if I know the exact positions of them in the ASCII file?

[HotChilli]

If I understand you correctly, from this-> "need to have 6,7,14,18,20,28,29" , it will combine 2 of your original columns into 1 when it imports to Power Query.

Hopefully, we can right-click on the column in the PQ editor and choose 'split column'.  There are a number of options from there to get the 2 columns required.

If you can't find one that works, post a sample of the combined columns and I can help.

[dal211]

Hi HotChilli,

 Very much appreciate your response. I apologize for not clarifying that well in my post, I've re-edited my post. 

The real issue here is that I need to import the text positions in ascending order as required by PowerBI, I technically have as an example 

6,7,14,18,20,21,28,28,29,31,36,39

 

This causes an error within PowerBI as the numbers 28 are repeated twice (...28, 28....). But I still want to include the variables "Time of Birth" - positions 21-28 and "DOB WK" - 25-28 in the delimiting process. This duplication of numbers happens frequently throughout my dataset.

If I want to make PowerBI accept my series of numbers, I have to choose between:

One series that ONLY includes "Time of Birth" positions 21-28

6,7,14,18,20,21,28,28,29,31,36,39

OR

One series that ONLY includes "DOB WK" positions 25-28

6,7,14,18,20,25,28,28,29,31,36,39

Thank you again for your help!