How to remove one semicolumn using DAX coding?

[toplisek]

I have a folder of files full of wrong added semicolumn. How to code and avoid those errors inside a source file like CSV?

[toplisek]

Since DAX operates after the data has already been loaded into the model, Dax is not the right tool to clean raw CSV data it operates after the data is loaded into Power BI. To fix this, Power Query (M code) is the appropriate layer, not DAX.

We manually solve a raw file.

[v-kpoloju-msft]

Hi @toplisek,

Thank you for the clarification and you're right. Since DAX is designed for calculations after the data has been loaded into the model, it's not suitable for cleaning raw CSV files. Power Query (M) is indeed the right place to handle that kind of data transformation before it enters your model.

Instead of continuing to manually edit the raw files outside of Power BI, you can automate the process within Power Query. This way, the cleaning steps will consistently apply every time you refresh the data no more manual intervention needed.

For example, if your raw file has invalid values like Age = 0 or extreme outliers (e.g., Age > 120), you can use this simple Power Query snippet to correct them:

M Code:

= Table.TransformColumns(Source, {

    {"Age", each if _ = 0 or _ > 120 then null else _, Int64.Type}

})

This replaces invalid age entries with null, which you can later treat or exclude in your visuals.

If you're dealing with specific types of errors or patterns, feel free to share an example I'd be happy to provide a customized M script to match your scenario.

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you for using Microsoft Community Forum.

[v-kpoloju-msft]

Hi @toplisek,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-kpoloju-msft]

Hi @toplisek,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[v-kpoloju-msft]

Hi @toplisek,

I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.

[v-kpoloju-msft]

Hi @toplisek,

Thank you for reaching out to the Microsoft fabric community forum. Also thanks @speedramps for his insights on this thread. I have identified several alternative workarounds that may assist in resolving the thread.

Based on your description, it sounds like the extra semicolons in your files are introducing unexpected blank columns when importing into Power BI, particularly from CSV or Excel formats. This can lead to parsing errors or data misalignment during the import process.

Since DAX operates after the data has already been loaded into the model, Dax is not the right tool to clean raw CSV data it operates after the data is loaded into Power BI. To fix this, Power Query (M code) is the appropriate layer, not DAX.

If you're working with Excel files, you can use the Excel.Workbook function in Power Query to access the sheet contents and then remove any extra columns caused by formatting inconsistencies. Here's the official Microsoft documentation for reference: Excel.Workbook – Power Query M

Use Text.Replace or Text.TrimEnd inside Power Query to clean unwanted semicolons from columns. Or use Table.RemoveColumns if an extra column is being created due to the trailing semicolon. Also please go through the below mentioned links for better understanding:
Text.Replace - PowerQuery M | Microsoft Learn
Text.TrimEnd - PowerQuery M | Microsoft Learn
Table.RemoveColumns - PowerQuery M | Microsoft Learn

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you for using Microsoft Community Forum.

[v-kpoloju-msft]

Hi @toplisek,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-kpoloju-msft]

Hi @toplisek,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[speedramps]

Have you tried removing the semicolons?

I want to help you more but your description is too vague 

You will get a quicker and better response without misunderstandings if you put time and effort into carefully writing a clear problem description with example input and output data. Look forward to helping you when this information is forthcoming

* Please DO give a simple non-technical functional description of what you want
* Keep it simple and break large projects into smaller questions and ask just one question per ticket.
* Rename columns to user friendly names. Avoid your own system jargon that we may not understand.
* Most importantly please provide example input data as table text (not a screen print) so helpers can import the data to build a solution for you. (Learn how to share data below)
* Provide the example desired output, with a clear step-by-step description of calculations and the process flow.
* Take time and care to use the same table and field names in the input, output and description so we can understand your problem and help you.
* Remove any unneeded tables, rows or columns which may cause confusion. Keep it short and concise with the minimal information regarding the key problem.
* Remember not to share private data ... we don't want you to get into trouble. ‌‌
* Please click the thumbs up button for these helpful hints and tips. Thank you.

Learn how to attach data in the forum using OneDrive:-
* Save your file in a OneDrive folder
* Right click on the file and click the “Share” blue cloud icon
* Click the bottom “Copy” button
* Click” Anyone with link can edit”
* Click “Can Edit”
* Click “Can View”
* Click “Apply” button
* Click “Copy”
* Paste the generated link via the forum, email, chat, or any other method.
* Helpers can then download your data, build a solution and share it back.

Learn how to attach data in the forum using Dropbox:-
1. Open Dropbox: Access the Dropbox folder on your computer or through the Dropbox web interface.
2. Select File/Folder: Find the file or folder you want to share.
3. Click Share (or Get Link): Look for a "Share" option or a similar "Get Link" option.
4. Choose Permissions: Decide whether to allow "view only" or "view and download" access.
5. Copy and Share: Copy the generated link and share it with anyone via the forum, email, chat, or any other method.

[toplisek · Accepted solution]

Thank you for the emessage!

I have manually modified the source file using programming Editor.