csv file column count parameter issue with tab / semicolon separator

[Goodkat]

Dear Power Query enthusiasts,

Yesterday I came across a topic I could not resolve with intense search on various forums. I have a csv file with 'tab' as delimiter.

When I Import it, the number of columns is detected and hard coded. If I remove that hard coded part the import works flawlessly and enables the number of columns to change. All good.
Now I want to connect another csv datasource to my model, but here the csv is split by semicolon. If I import the column count gets hardcoded into. If I, similarly to the tab separated file, remove the hard coded column count, the import fails as it only shows the first 2 and not full 11 columns.

I played around a bit with the 'Encoding' parameter, but did not succeed. What is the reason for this different behavior just by different delimiters.
How can I overcome that (without transpose, filter and count filled header entries) ?

https://c.gmx.net/@324888734501700174/EapEm3mEefSQjWb3K4Elgg

Thank you very much for your time and experience while looking at it.

Best regards, Andreas

[Goodkat]

Dear MFelix,

Thank you so much for your reply! You put a lot of effort into your reply! I do see and appreciate that, as it did help me to understand the root cause of the problem now. Also your suggestion with the caveat that the 'split column by delimiter' will hardcode the number of columns by the need to assign names is very helpful and I totally see that limitation now.

By your thorough answer I now can stop going down that road and find another solution. (But first I MUST put your reply into my personal 'Power Query encyclopedia' to avoid that confusion in future 😉

Have a great weekend and stay healthy!

Best regards, Andreas

[Omid_Motamedise]

When Power Query uses Csv.Document, it infers the structure based on delimiter and encoding. If it detects the delimiter incorrectly or encounters encoding issues (like mismatched quotes or BOM), it may parse fewer columns than exist.

In your case, the semicolon-delimited file likely contains quoted fields, and Power Query is not recognizing the delimiter properly because of how Csv.Document is being called.

You can explicitly define the delimiter, encoding, and quote style using a custom Csv.Document call without hardcoding the columns.

Here’s a clean, dynamic solution:

let
Source = File.Contents("C:\YourPath\semicolon_file.csv"),
Csv = Csv.Document(Source,
[Delimiter=";", Columns=11, Encoding=1252, QuoteStyle=QuoteStyle.Csv]),
PromoteHeaders = Table.PromoteHeaders(Csv, [IgnoreErrors=true])
in
PromoteHeaders

[Goodkat]

Dear Omid,

thank you for your reply. But in your clean dynamic solution the columns are hardcoded via 'Columns=11' or do I oversee something in that regard?

So it does not solve the issue.

Best regards, Andreas

[Goodkat]

Dear MFelix and CST,

thank you for your replies. I tried the tips given and also reviewed the videos. Without success. I also realized that I did not post the original soure data which might be necessary to replicate the topic.

I will continue to work on that and would be happy if you could find the time to replicate with the original data sources.

https://deref-gmx.net/mail/client/f5V5xj0l31E/dereferrer/?redirectUrl=https%3A%2F%2Fc.gmx.net%2F%403...
https://deref-gmx.net/mail/client/-INmjWVLxUg/dereferrer/?redirectUrl=https%3A%2F%2Fc.gmx.net%2F%403...

Thank you!

Best regards, Andreas

[MFelix · Accepted solution]

Hi @Goodkat ,

The question about your files and when you remove the column numbers is related with the first row of your file.

In the TAB one you have the 2025 being in the end of the line with a tabe for each of the columns. Not sure if it's visible on this image but you can check the indentation on the first line:

So it does not matter if you have the number of columns or not because there are the correct number of separators in the first line. If you delete all the tabs in the first row you will see that the final result will be a single column:

When you get to the semicolon this is not what is happening:

You can see that there is only a semicolon in the first row so it splits by 2 when you remove the column number.

In both cases if you want to make sure you have the correct number of columns you must:

• Connect to the CSV
• Select a separator that is not on the file

• Remove the top 2 rows

• Split columns by delimiter
• Promote headers

Be aware that this option will also hard code the number of columns because of the column names that will be hard coded into that specific function. You can try and write some specific function to get a dynamic number of columns based on the number of semicolon or tab or whatever other separator.

[MFelix]

Hi @Goodkat ,

I have played around with CSV with different separators and was not able to replicate that problem when removing the number of columns from the function.

Here a couple of links to some videos that maybe can give a pointer on what is happening, vbe aware that they don't add anything to the steps you created but mayvbe you can try and check if some of them give you an explanation on what is happening. In the last one you have a workaround using split to columns.

https://www.youtube.com/watch?v=ISsL0lDobT8

https://m.youtube.com/watch?v=d7lek-fBrkM&t=98s

https://www.youtube.com/watch?v=SlVNma5QIw0

[v-ssriganesh]

Hello @Goodkat,
Thank you for reaching out to the Microsoft Fabric Forum Community.

The issue with your semicolon-separated CSV file, where only 2 of 11 columns are detected after removing the hardcoded column count, is likely due to Power Query misinterpreting the semicolon delimiter, possibly influenced by regional settings or file encoding.

To resolve this, modify your query to explicitly set the delimiter to a semicolon and use UTF-8 encoding in the Power Query Editor’s import settings. Ensure the column count parameter is removed to allow dynamic detection of all 11 columns, like your tab-separated file and FYI, the csv file you shared was empty.

If this information is helpful, please “Accept as solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.