Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
KrisCachia
Regular Visitor

powerbi datatypes problem with SharePoint List

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 

 

1 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. 

 

View solution in original post

7 REPLIES 7
vanessafvg
Super User
Super User

can you share your power query code?





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




hi,

 

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

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.





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




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. 

 

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.'





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




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. 

 

 

 

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"

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.