Formatting Column with data of various types to be countable in visualizations

[Let-Me-Know]

Hi everyone, I am quite new to Power BI and would appreciate your help!

I have problem with transforming data type in specific column in Power Query Editor. Source for the data is Excel table and the specific column “Transaction ID” contains combined data types, for example:

Transaction ID:

• 01012023
• HR01012023
• 01AA012023
• Etc.

All of the values are proper and I need them all as they are. Also, later in visualizations I need them to be countable, so that I can count or sum up transactions per month or year etc. In Excel table (source), number format is set on “General”. Once I load it in Power BI, Editor automatically defines the format type for that column as number and therefore Error message appears saying that values cannot be converted to number.

I have tried formatting them in Excel source as text, however that way I can’t count or sum up those transactions in visualizations. I have also considered splitting them in separate columns in Excel by criteria of text and number, but I’m not sure if that would help (once I merge them together again), and also there is a problem with splitting all of them correctly since I don’t have unique pattern of value containing for example 2 letters and then 6 digits. If one of these would be efficient, I don't know the way to accomplish it correctly.

To summarize, how to format this column in Power Query Editor to be countable later in visualization?

Thank you in advance!

[v-jingzhang · Accepted solution]

Hi @Let-Me-Know 

Once you connect to an Excel file, there is usually a "Changed Type" step which is generated automatically. In this step your [Transaction ID] column is changed into Number type. Please remove this step by clicking the "×" next to it and change this column to Text type manually from Transform > Data Type. Change the data type of other columns manually too. 

Text data type will not affect the result of COUNT aggregation. When you want to do a sum up, it should be on another number type column in the table right? It's meaningless to sum up the [Transaction ID] column. Please check whether the column to be summed up has been changed to number type in advance. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[ronrsnfld · Accepted solution]

In the Power Query editor there will be a step titled #"Changed Type"

If you open the Advanced Editor, you will see a line looking something like:

#"Changed Type" = Table.TransformColumnTypes(Source,{{"Transaction ID", type number},{"Other column Names", type...}}),

Merely change type number to type text

[Let-Me-Know]

Thank you, very helpful for me! That is basically the same as @v-jingzhang solution: manually editing what PowerBI automatically generated, or just removing that automatically generated step!

[ronrsnfld]

The difference between our two solutions is if you have other columns, my solution only changes the transaction ID column, whereas his removes data type from all columns and then only adds back to the transaction ID column.

[Let-Me-Know]

You're right, now I get it! Thanks once more!

[v-jingzhang · Accepted solution]

Hi @Let-Me-Know 

Once you connect to an Excel file, there is usually a "Changed Type" step which is generated automatically. In this step your [Transaction ID] column is changed into Number type. Please remove this step by clicking the "×" next to it and change this column to Text type manually from Transform > Data Type. Change the data type of other columns manually too. 

Text data type will not affect the result of COUNT aggregation. When you want to do a sum up, it should be on another number type column in the table right? It's meaningless to sum up the [Transaction ID] column. Please check whether the column to be summed up has been changed to number type in advance. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[Let-Me-Know]

Thank you so much! I have considered and tested so many rather complicated options, and the solution was so simple: just removing PowerBI's automatically generated change.