- #Microsoft office 2013 pro powerpivot power query software
- #Microsoft office 2013 pro powerpivot power query code
- #Microsoft office 2013 pro powerpivot power query professional
You can then add Schema Sample Size to the connection string. This will bring up the Connection Properties. Then on the Import Data Dialog, you want to click on Properties. On the Save Data Connection File and Finish, you want to hit Finish. Select the table that you want and click Next. You want to get the Data Connection Wizard up by doing to the Data Tab, From Other Sources and From OData Data Feed. Then just continue with the import.įor the Excel Data Tab, it isn’t as obvious. Within Power Pivot, we want to click on Advanced on the Table Import Wizard, after we fill in our Data Feed URL.įrom there you can adjust the Schema Sample Size to a value that fits your needs.
#Microsoft office 2013 pro powerpivot power query code
Here is what it looks like when I set Schema Sample Size to 50 using the same test code that I wrote. Well, we saw that we didn’t have a value until row 32, so that is a problem. There is a connection string property for DataFeedClient called “Schema Sample Size”. If that is the case, DataFeedClient will just default to a string data type. In our case, the values of the first 31 rows for ProjCost2 and ProjCost3 were blank. Instead, it sniffs the first n rows of a data set. The problem here is that DataFeedClient doesn’t currently use the schema described by an OData Service. So, this was definitely something that the DataFeedClient was using. Using ( conn = new DataFeedConnection(connBuilder.ConnectionString)) So, I spun up some code to use it and see what the schema looked like coming back from the DataFeedClient. Like some sort of post import processing to determine data types. One thing I wanted to understand is whether this issue of it thinking it was a string data type was due to the DataFeedClient, or something Power Pivot was doing after the data was pulled in. Excel displays the COM Add-Ins dialog box that. Click the Manage drop-down list button and then select COM Add-Ins from the drop-down list before you select the Go button. Excel opens the Add-Ins tab of the Excel Options dialog box with Excel Add-Ins selected in the Manage drop-down list. So, that explains why I saw the same behavior. Choose FileOptionsAdd-Ins or press Alt+FTAA. Under the hoods, grabbing an OData Feed from the Excel Data Tab is actually just using Power Pivot to get it. The PivotTable here showed the same behavior that the Excel Data Tab did. We also saw that the data type here was showing text for the ProjCost 2 and ProjCost3 fields. Looking at Power Pivot, we actually saw the same behavior as with the Excel Data Tab. However, the customer didn’t want to use Power Query. The 5 fields looked consistent.Īlso, in the Query itself it was showing “Number” as the data type. Grabbing the data already looked different from what we saw from the Excel Data Tab pull. Next I wanted to see what Power Query would do. The ProjCost2 and ProjCost3 fields were just showing a 0. Also, when we pulled this into a PivotTable and summed the ProjCost fields, we hit some odd behavior. You’ll notice that ProjCost2 and ProjCost3 show decimals as compared to 1, 4 and 5. Once the data is imported it looked like the following: The customer was originally pulling this in via the Excel Data Tab. Here is the schema we had for our internal repro. The issue we had is that for some of the fields in the feed had a data type of Decimal. This could be through the Excel Data Tab, Power Pivot or Power Query. Within Excel, we have multiple ways to import data. This actually related to a Project Server hosted in a SharePoint 2013 On Premise deployment. Visit >here for more information and to book today.We had a customer that was trying to pull data into Excel by way of an OData feed. You can find out more about Power Query, Power Pivot and Power BI on one of our training courses. Presently, this includes all Power Query features, except for the following:
Power Query Public: Available for all other Office Desktop Stock Keeping Units (SKUs).
#Microsoft office 2013 pro powerpivot power query professional
This is presently available for Professional Plus, Office 365 ProPlus or Excel Standalone only
#Microsoft office 2013 pro powerpivot power query software
Sometimes, you think you have the software all figured out and Microsoft goes and moves it all! There’s usually new Power Query updates monthly and we mention these in our newsletter as they arise. Finally, check the ‘Microsoft Power Query for Excel’ add-in and you are away!