Number format when using "From a File" Import

joemolez
Posts: 3
Joined: Tue Jul 31, 2018 2:24 am

Number format when using "From a File" Import

Postby joemolez » Tue Jul 31, 2018 2:30 am

Hi,

I imported multiple text files into Power Query using "From a File" Import option. I want to use the numbers stored as text in my Excel worksheet.

Power Query seems to have difficulties converting numbers stored as text in this situation... Having a look at the screenshots: using the "Data type" option, and converting the text to decimal numbers, this does not solve the problem, although non-decimal values are conveniently converted.

Does anyone have a custom function, or a solution to this?
Attachments
Screenshot_1.PNG
When everything is in "text" format
Screenshot_1.PNG (146.22 KiB) Viewed 471 times
Screenshot_2.PNG
When converting to numbers
Screenshot_2.PNG (134.54 KiB) Viewed 471 times

MattAllington
Posts: 967
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Number format when using "From a File" Import

Postby MattAllington » Tue Jul 31, 2018 7:34 am

Can you post some sample data?
Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training

joemolez
Posts: 3
Joined: Tue Jul 31, 2018 2:24 am

Re: Number format when using "From a File" Import

Postby joemolez » Fri Aug 03, 2018 6:37 am

Hi Matt,

Here is some data to work with. At the end of my response is the initial data, as I could not attach .txt or .dat files.

You can see, in the Excel worksheet (not in the power query window) that Excel recognizes the value as a number.

But if you try to plot a graph, or use the value in a formula, it is all counted as zeros...

Many thanks in advance for you help

***Input data***

History 7 vs 6
----------------------------------
fs (FISH) vmax1 (FISH)
--------------------- ---------------------
8.5000000E-001 1.6542877E+000


History 7 vs 6
----------------------------------
fs (FISH) vmax1 (FISH)
--------------------- ---------------------
9.0000000E-001 1.6542877E+000


History 7 vs 6
----------------------------------
fs (FISH) vmax1 (FISH)
--------------------- ---------------------
9.5000000E-001 1.6542877E+000


History 7 vs 6
----------------------------------
fs (FISH) vmax1 (FISH)
--------------------- ---------------------
1.0000000E+000 1.6542877E+000


History 7 vs 6
----------------------------------
fs (FISH) vmax1 (FISH)
--------------------- ---------------------
1.0500000E+000 1.6542877E+000


History 7 vs 6
----------------------------------
fs (FISH) vmax1 (FISH)
--------------------- ---------------------
1.1000000E+000 1.6542877E+000


History 7 vs 6
----------------------------------
fs (FISH) vmax1 (FISH)
--------------------- ---------------------
1.1500000E+000 1.6542877E+000


History 7 vs 6
----------------------------------
fs (FISH) vmax1 (FISH)
--------------------- ---------------------
1.2000000E+000 1.6633353E+000


History 7 vs 6
----------------------------------
fs (FISH) vmax1 (FISH)
--------------------- ---------------------
1.2500000E+000 1.7093436E+000


History 7 vs 6
----------------------------------
fs (FISH) vmax1 (FISH)
--------------------- ---------------------
1.3000000E+000 1.7749432E+000


History 7 vs 6
----------------------------------
fs (FISH) vmax1 (FISH)
--------------------- ---------------------
1.3500000E+000 1.8988127E+000


History 7 vs 6
----------------------------------
fs (FISH) vmax1 (FISH)
--------------------- ---------------------
1.2500000E+000 1.7093436E+000


History 7 vs 6
----------------------------------
fs (FISH) vmax1 (FISH)
--------------------- ---------------------
1.3000000E+000 1.7749432E+000


History 7 vs 6
----------------------------------
fs (FISH) vmax1 (FISH)
--------------------- ---------------------
1.3500000E+000 1.8988127E+000


History 7 vs 6
----------------------------------
fs (FISH) vmax1 (FISH)
--------------------- ---------------------
1.4000000E+000 2.0480278E+000


History 7 vs 6
----------------------------------
fs (FISH) vmax1 (FISH)
--------------------- ---------------------
1.4500000E+000 2.2122473E+000


History 7 vs 6
----------------------------------
fs (FISH) vmax1 (FISH)
--------------------- ---------------------
1.5000000E+000 2.4139537E+000


History 7 vs 6
----------------------------------
fs (FISH) vmax1 (FISH)
--------------------- ---------------------
1.5500000E+000 2.6563513E+000


History 7 vs 6
----------------------------------
fs (FISH) vmax1 (FISH)
--------------------- ---------------------
1.6000000E+000 2.9631244E+000


History 7 vs 6
----------------------------------
fs (FISH) vmax1 (FISH)
--------------------- ---------------------
1.6500000E+000 3.3274494E+000


History 7 vs 6
----------------------------------
fs (FISH) vmax1 (FISH)
--------------------- ---------------------
1.7000000E+000 3.7606226E+000


History 7 vs 6
----------------------------------
fs (FISH) vmax1 (FISH)
--------------------- ---------------------
1.7500000E+000 4.2158511E+000


History 7 vs 6
----------------------------------
fs (FISH) vmax1 (FISH)
--------------------- ---------------------
1.5500000E+000 2.6563513E+000


History 7 vs 6
----------------------------------
fs (FISH) vmax1 (FISH)
--------------------- ---------------------
1.6000000E+000 2.9631244E+000


History 7 vs 6
----------------------------------
fs (FISH) vmax1 (FISH)
--------------------- ---------------------
1.6500000E+000 3.3274474E+000


History 7 vs 6
----------------------------------
fs (FISH) vmax1 (FISH)
--------------------- ---------------------
1.7000000E+000 3.7606226E+000


History 7 vs 6
----------------------------------
fs (FISH) vmax1 (FISH)
--------------------- ---------------------
1.7500000E+000 4.2158511E+000


History 7 vs 6
----------------------------------
fs (FISH) vmax1 (FISH)
--------------------- ---------------------
1.8000000E+000 4.8116887E+000


History 7 vs 6
----------------------------------
fs (FISH) vmax1 (FISH)
--------------------- ---------------------
1.8500000E+000 5.9848576E+000


History 7 vs 6
----------------------------------
fs (FISH) vmax1 (FISH)
--------------------- ---------------------
1.9000000E+000 9.9556815E+000


History 7 vs 6
----------------------------------
fs (FISH) vmax1 (FISH)
--------------------- ---------------------
1.9500000E+000 9.2673853E+001


History 7 vs 6
----------------------------------
fs (FISH) vmax1 (FISH)
--------------------- ---------------------
2.0000000E+000 2.8392483E+002


History 7 vs 6
----------------------------------
fs (FISH) vmax1 (FISH)
--------------------- ---------------------
2.0500000E+000 4.9347303E+002
Attachments
From_a_file_import_problem.xlsx
(19.1 KiB) Downloaded 7 times

MattAllington
Posts: 967
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Number format when using "From a File" Import

Postby MattAllington » Fri Aug 03, 2018 1:41 pm

Interesting. What Locale are you using on your PC? If you select both columns after loading to Power Query, then select both columns, right click, Change Type to Decimal "using locale" and it will work.
Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training

joemolez
Posts: 3
Joined: Tue Jul 31, 2018 2:24 am

Re: Number format when using "From a File" Import

Postby joemolez » Sat Aug 04, 2018 12:35 am

This solved the problem. I was in en-ca (english for canada) and switched to en-us.

Many thanks for helping solve my problem :mrgreen:


Return to “Power Query”

Who is online

Users browsing this forum: No registered users and 1 guest

cron