powerbi datatypes problem with SharePoint List

[KrisCachia]

Hi all,

I've got something really wierd going on when i connect to a sharepoint list from power bi. 

When i connect to the list all the field datatypes come up as text values... 

Cant understand why fields which are number data types in the sharepoint list are opening as text in powerbi 

Any help would be appreciated 

[vanessafvg]

can you share your power query code?

[KrisCachia]

hi,

im rather new to powerbi. Is the code displayed in the Advanced editor window?

[vanessafvg]

yes that is correct.   althought to be fair i am not 100% sure it will  pull it through as the correct data types, power query usually if you have that setting on will automatically do a convert  for you based on the first few hundred rows of data, it will decide what data type it should be and that will have a change type step.  but if you can share the power query code as you illusted in the advanced editor window that would be useful to understand the steps.

[KrisCachia]

one thing i should have mentioned, i created the Sharepoint list using the upload from excel feature. Maybe the cells in the spreadsheet were set as text, however during the upload, the fields were detected as number datatypes, and the sharepoint list datatypes were created correctly. 

Should i try doing the import again and make sure the source file in excel columns are formatted correctly ? OR create the sharepoint list manually from scratch? havent tried any of these things yet. 

[vanessafvg]

https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-data-types

actually I think what you experiencing is correct, however it should do an automatic conversion

'This article describes data types supported in Power BI Desktop and Data Analysis Expressions (DAX).

When you load data into Power BI Desktop, it will attempt to convert the data type of the source column into a data type that better supports more efficient storage, calculations, and data visualization. For example, if a column of values you import from Excel has no fractional values, Power BI Desktop will convert the entire column of data to a Whole Number data type, which is better suited for storing integers.

This concept is important because some DAX functions have special data type requirements. While in many cases DAX will implicitly convert a data type for you, there are some cases where it will not. For instance, if a DAX function requires a Date data type and the data type for your column is Text, the DAX function will not work correctly. So, it’s both important and useful to get the correct data type for a column. Implicit conversions are described later in this article.'

[KrisCachia · Accepted solution]

thanks for the link. I will investigate abit alternatives because it is the first time i created a sharepoint list using excel upload feature. In most other Sharepoint Lists created manually from scratch, never had this issue in powerbi. 

[KrisCachia]

I simply connected to the SharePoint List. Didnt do any other settings. Here is the code:

let
Source = SharePoint.Tables("companysiteurl", [Implementation=null, ApiVersion=15]),
#"1e4fcf33-6d58-4fd4-bb9d-61306e5da796" = Source{[Id="1e4fcf33-6d58-4fd4-bb9d-61306e5da796"]}[Items]
in
#"1e4fcf33-6d58-4fd4-bb9d-61306e5da796"