Format power query colum

[uk747]

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

 

[uk747]

Test Data in Testdbase.xlsb
testdbase and workbook containing query are both formatted the same

	A	B	C	D	E	F	G
1	Name	Joined	Sales	Joined 2	Sales 2	Profit	Profit2
2	alan	06 May 22​	8​	05 Jun 22​	1​	4.30​	8.30​
3	bob	07 May 22​	5​	06 Jun 22​	4​	5.20​	2.50​
4	colin	n/a	3​		7​	6.10​	1.40​
5	dave		n/a	08 Jun 22​		n/a
6	edna	08 May 22​	2​	09 Jun 22​	3​	4.00​	1.80​
7	fred	09 May 22​	1​	10 Jun 22​		n/a	6.70​
8	gary	n/a			2​	n/a	3.10​
9	hilary	10 May 22​	3​	12 Jun 22​	4​	8.10​	2.20​
10	ian	10 May 22​	8​	13 Jun 22​	7​	2.20​	4.80​

Original

let
    Source = Excel.Workbook(File.Contents("C:\mydocs\Testdbase.xlsb""), null, true),
    Sheet2 = Source{[Name="Sheet1"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet2, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Name", type text}, {"Joined", type text}, {"Sales", type text}, {"Joined 2", type date}, {"Sales 2", Int64.Type}, {"Profit", type text}, {"Profit2", type number}})
in
    #"Changed Type"

Which Show Joined and Sales and Profit as Text(ABC), Joined 2 as Date, Sales 2 as Whole Number and Profit 2 as Decimal Number

When I change to code to below the Joined, Sales and Profit Column were still Text in the spreadsheet although in PQ the headings had changed to ABC123

let
    Source = Excel.Workbook(File.Contents("C:\mydocs\Testdbase.xlsb""), null, true),
    Sheet2 = Source{[Name="Sheet1"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet2, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Name", type text}, {"Joined", type any}, {"Sales", type any}, {"Joined 2", type date}, {"Sales 2", Int64.Type}, {"Profit", type any}, {"Profit2", type number}})
in
    #"Changed Type"

even when i removed the columns from the query it was the same

let
    Source = Excel.Workbook(File.Contents("C:\mydocs\Testdbase.xlsb""), null, true),
    Sheet2 = Source{[Name="Sheet1"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet2, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Name", type text}, {"Joined 2", type date}, {"Sales 2", Int64.Type}, {"Profit2", type number}})
in
    #"Changed Type"

 

[RoryA]

This seems to be an issue with using xlsb files as the source (a similar thing happens with csv files). Using the same file, one as xlsx and one as xlsb, the former behaves correctly whereas the latter does what you describe. Can you change the format of the source file? If not, I suspect you'll need to create a calculated column for each column that has mixed data in it.

 

[uk747]

ok thanks. Didn't realise these issues were specific to csv and xlsb

Changing the file and query to .xlsm seemed to work although the reason I used xlsb was because it made the file alot smaller when lots of data

Also when data was transferred from a textbox that had a property of Multiline. If the data transferred had multiple lines by using the Enter key, it looked fine in the testData but when it came back it had below in cells that had multilines

the cell was
First line_x00D_
Second line

although I did resolve by using #"Replaced Value" = Table.ReplaceValue(#"Changed Type","_x000D_","",Replacer.ReplaceText,{"headings"})