How to remove scientific notation from Number Column in query editor

[lauren_visarend]

I have tried to change the locale to english(us), rounding, transforming the text first, etc. 

These are ID's, so I need them to build relationships between tables. They should not be in scientific notation, and I can not figure out how to show them as a true whole number. 

The dataset is too large to keep the column as text, I need to convert these ID's to number format to help with memory issues.

[parry2k]

@lauren_visarend it doesn't make a difference to converting these to text.

[lauren_visarend]

@parry2k Is there not a memory performance difference between the storage size of a text field vs. numeric field? I initially followed this Microsoft Document to try and achieve better memory performance which suggests optimizing column types. https://docs.microsoft.com/en-us/power-bi/guidance/import-modeling-data-reduction

"The VertiPaq storage engine uses separate data structures for each column. By design, these data structures achieve the highest optimizations for numeric column data, which use value encoding. Text and other non-numeric data, however, uses hash encoding. It requires the storage engine to assign a numeric identifier to each unique text value contained in the column. It is the numeric identifier, then, that is then stored in the data structure, requiring a hash lookup during storage and querying."

My Virtual Machine specs:

32gb ram 

Processor Intel(R) Xeon(R) CPU E5-2630 v3 @ 2.40GHz, 2394 Mhz, 4 Core(s), 4 Logical Processor(s)

I am updated to the March 2020 version of PowerBI Desktop

I am importing 5 tables, 3 of which have 200-500 million rows of data and 5-8 columns. 

2 of the tables have less than 500 rows of data, with 10 columns. 

At this point, I have not added any measures/dax calculations.

I did have to reformat the $$ columns to divide the column by 100 and change the column type to Fixed Dec. Number because the raw data has all values x100 (IE, $1.00 is 100 in the raw data)

I'm connecting to an Azure SQL Database for the raw data. 

If you have any other suggestions to improve performance and keep the ID columns as text fields, that would be great! Do I need to upgrade my VM again, or is there something obvious that I am missing in my load process? 

[parry2k]

@lauren_visarend Based on your input I would recommend to use aggregations in Power BI, read more here. I believe that is the best solution for you rather than full import.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!