Format power query colum

uk747

Well-known Member
Joined
Jul 20, 2011
Messages
843
Office Version
  1. 365
Platform
  1. Windows
Hi

When I import any number data it formats ok only if every cell in the column is a number, but if 1 or more is text, the whole column becomes text

How can I format it the same as source file which is formatted as number and any text in the column Doesnt affect it

Doesn't matter if it's dates integers, numbers 1.23, % or times 1:23 and both columns are formatted the same it happens
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
You can't format it as a number if some of the data is not numeric. You'd have to use the Any type.
 
Upvote 0
I couldn't see ANY type under change type

Would that show values as values and text as text
 
Upvote 0
Yes it would. The simplest way is just to remove any mention of that column from your Changed Type step.
 
Upvote 0
Not sure what you mean. I want to show all columns after import

I edited a column and changed type to decimal, but on the sheet even though the whole column is formatted as number it shows as text (left side of cell)
 
Upvote 0
Where are you importing from?
 
Upvote 0
Another excel sheet.

Issue is if a column mostly full of numbers has one text value the whole column may show as the number but is actually text

Same for dates. Even though it will look like a proper date i.e. 07 Feb 22 it is actually text
 
Upvote 0
Is there a step in your query called Changed Type? There should be if you did a default query from another workbook. If so, you just need to either remove any mention of the number column from that step, or change the specified type for that column from text to any
 
Upvote 0
Is the only place you can change type to any in the query itself, as when you right click column headers and change type, there is no option for any

I did change within the query

Was originally
#"changedtype" = table.transformcolumntypes(#"promoted headers", {{"name", type text}, {"my date", type text}

Tried
{"my date", type any} and
{"my date", any.type}

Also tried deleting the reference to the column in the query. When I refreshed it, it was still text
 
Upvote 0

Forum statistics

Threads
1,223,677
Messages
6,173,778
Members
452,534
Latest member
autodiscreet

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top