Displaying SharePoint 2010 multi-value choice columns in one query column
In my work environment SharePoint 2010 is still very much alive and I run several Power Query scenarios with SharePoint 2010 data. Yesterday, I worked on a query that includes a multi-value choice column. In the Query Editor, the column shows as "Table" and when that column is expanded, will duplicate each row for each value in the multi-value SharePoint column. That's not what I wanted. I'd like to see the values from the choices in one row, separated with a comma. Searching the web brought me to forum threads that discuss just that, but they were talking about "List" where I see "Table". I guess that is a difference between SharePoint 2010 and later versions, where apparently SharePoint choice columns are returned as "Lists" to Power Query. Anyway, the suggestion to use ... Text.Combine([Choices], ", ") ... did not work for the Table column. It threw an error instead. "Expression.Error: We cannot convert a value of type Table to type List." In Power Query, lists are one-column objects, whereas tables are multi-column objects, and even if a table contains only one column, it must be explicitly referenced. It took me a little more digging in the search engines, but then I found the solution that works with SharePoint 2010 multi-value choice columns, which show up as tables in Power Query. In the first screenshot we can see the selected table in the lower part of the Power Query window. The column header for the data I want to extract is "Value". So all I needed was to reference "Value" column in the formula. The correct syntax for that is Text.Combine([Choices][Value], ", ") And, hey, presto! It worked as I expected. Hope that helps someone.PowerView Multiples : Greyed out => not available
While giving a Training related to PowerView I couldn't explain the option Multiples, because they were greyed out. When selecting fields, Power View itself decides to place the field in the multiples. That I can accept, but it was impossible to move the field, delete the field, ar place another field in the option Remark: it was op the classroom PC not on my personal PC Someone got an idea?"The input couldn't be recognized as a valid Excel document."
Such alert could appear when you try to use Power BI connector on Excel file. It's understandable if the source file is corrupted and can't be opened in Excel. However, it looks strange if Excel opens the file in question and shows nothing wrong. Based on our experience above is usually means what something is wrong with XML scheme of the Excel workbook. Mushup trace (Data->New Query->Query Options->Diagnostics->Enable tracing) could give some additional information, but often not enough to find the reason. We had two main scenarios XML scheme is not complete Usually if Excel file was generated by third-party tool. Such tool could generate quite limited XML scheme which is enough to open the file in Excel and to work with it, but not enough for Power BI connector. As an example, trace log shows [DataFormat.Error] The input couldn't be recognized as a valid Excel document.\r\nStackTrace:\n… … [DataFormat.Error] We couldn't find a part named '/xl/sharedStrings.xml' in the Excel package.\r\nStackTrace:\n… Such case is easy to fix – it's enough to open the file in Excel and save it (without any changes) – Excel is clever enough to fix the scheme. For the routine regular tasks we use poweshell script which does exactly the same in background. There is the link within Excel file which is not recognizable as valid Usually if Excel file is synced/kept with some cloud storage. One of the variants, wrong link could appear with copy/paste from another such file. That could be active link in one of the cells; or the link within conditional formatting formula; or even the link which actually isn't used by Excel but kept somewhere inside the scheme. For example, in one of the files I found in Data->Consolidate->All references the link like '\\drive.tresorit.com@7235\Tresors….\[file.xlsx]Sheet'!$AC$6:$AC$357 on the file which was deleted long ago and isn't used, but for some strange reason the link was kept within the scheme. Unfortunately for such case trace log doesn't give enough information to localize the issue, it looks like [DataFormat.Error] The input couldn't be recognized as a valid Excel document.\r\nStackTrace:\n… … nExceptionType: System.UriFormatException, System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089\r\nMessage: Invalid URI: The hostname could not be parsed.\r\nStackTrace:\n Perhaps I have not enough knowledge for more straight forward localization of the problem, but the only way is to exclude Excel file parts one by one and check if the issue disappeared. Another way could be to unzip Excel file and check if wookbook.xml or sheetNN.xml have something suspicious inside.Intro to the Excel Data Model
Here is a great new Excel lab hosted on TechNet Labs. Learn about the data model, for free. This lab is intended to serve as an introduction to the understanding the Excel Data Model. The Excel data model was available in Excel 2010 and was introduced together with an add-in to Excel called Power. It has the data model in it and in the next version of Excel 2013, the model was actually absorbed into Excel and now it’s part of Excel itself. To really use the Excel Data model you need to understand the DAX language, which is the formula language for the Excel Data Model. This Lab will not walk through DAX. For further in-depth DAX learnings, please check out the following resources: The DAX Resource Center Wiki (http://social.technet.microsoft.com/wiki/contents/articles/dax-resource-center.aspx). Data Analysis Expressions (DAX) Reference (https://support.office.com/en-us/article/Data-Analysis-Expressions-DAX-Reference-411c6891-614d-438c-bf45-c7e061dd9e08). The DAX in the BI Tabular Model whitepaper (http://go.microsoft.com/fwlink/?LinkID=237472&clcid=0x409) The lab will walk through viewing the data model, setting up relationships, creating a date dimension and a set of calculations. Take tyhe lab now!Faster OLAP PivotTables in Excel 2016
If you work with connections to OLAP servers, your PivotTables are now faster. The June 2016 update contains query and cache improvements to this powerful feature. You could benefit from these improvements, whether you use PivotTables to answer one-off questions or build complicated workbooks with dozens of PivotTables. These updates are available to Excel 2016 users as part of an Office 365 subscription. If you are already an Office 365 subscriber, find out how to get these latest updates. You’ll also need a workbook that has PivotTables connected to either the Data Model or to an OLAP server. About the improvements We have made significant improvements in three major areas while querying OLAP servers. Improved query efficiency—Excel will query for subtotals only if they’re required to render the PivotTable results. This means you wait less for the OLAP server to finish processing the query and spend less time waiting for the results to transfer over your network connection. Reduced the number of queries—Excel is smarter when refreshing your data. It now uses the LAST_SCHEMA_UPDATE property of a connection to limit schema requests to those times when it has, in fact, changed. Smarter caches—When the schema is retrieved, it is now shared across all of the PivotTables on that connection, further reducing the number of queries. Get the full story here https://blogs.office.com/2016/07/07/faster-olap-pivottables-in-excel-2016/Get & Transform - Introduction Video
Get & Transform (Power Query) is amazing at connecting to and transforming data. You just build the connections, do the transformations once, and then simply refresh anytime you want to update the data. Power Query is built into Excel 2016 and is a free download for Excel 2010 and 2013 Check out this 4-minute video which steps through the process of combining the data from every CSV file in a folder into one table, then impress your colleagues as you replicate this demo for them!
Sample Power Pivot and Power BI Desktop Models
Hi, I've recently built and published two sample data models - one with Power Pivot for Excel and another with Power BI Desktop to support training and testing of model and report authoring features in both tools (e.g. DAX metrics, chart/visual types). Both models use the https://msdn.microsoft.com/library/mt734199(v=sql.1).aspx sample data warehouse database for SQL Server 2016 as the source and they share the same schema and metadata. Each can be downloaded from the following two links: http://tinyurl.com/z2gyhps https://insightsquest.com/2016/10/08/sample-power-bi-desktop-model-available-for-download/ The models contain multiple star schemas so they may be a bit larger than common XLSX and PBIX models but this also makes them more powerful and useful for learning and understanding evaluation context. I hope these can be of value but please feel welcome to comment here or on my blog if you have problems or issues. Isn't it great that we TWO great BI tools like this? -BrettExcel and Power BI – better together
Great new feature (preview) is introduced in Apr update of Power BI Desktop. Now we may connect directly to the dataset published in the Power BI service. Other words – create and support the data model in Excel, and generate visualization in Power BI Desktop connected to such model. Exactly the way I prefer to do.Get & Transform - solving all sorts of Excel issues
Some may see Get & Transform (Power Query) simply as a brilliant way of connecting to multiple data sources and loading that data into a Microsoft Power BI development environment (Excel Power Pivot or Power BI desktop). Having spent 25 years using Excel / Lotus 123 functions and macros to clean up crappy data and automate manual tasks I find it is so much more. It is a breath of fresh air. There are lots of great people out there giving great advice on how to get the most out of Get & Transform and I salute them all. A big thank you. Get & Transform can solve all sorts of problems for the Excel user who is wrangling with data. Consolidating, Splitting, Merging, Replacing, Filtering, "UNPIVOTTING!"... the list is comprehensive and seems to be ever growing. Here's just one small and very unusual example. It's the "unusual-ness" that prompted this article. It hopefully goes to show that if there's a problem that you don't know how to solve using "normal" Excel then Get & Transform may well be the answer. The Problem A table of data had been entered in Excel but rather than a unique row being used for each value the user has entered data using the Alt+Enter trick to transfer data onto the next line in the same cell. This makes it impossible to create totals (OK not impossible but you need an array formula and some "out-there" thinking to get it done). Get & Transform to the rescue.... 1.Turn the original set of data into a Table (highlight and Ctrl+T) 2. Get & Transform > From Table 3. Then click on the Split Column > Split by Delimiter And select the Custom Option followed by ticking the box Split using special characters and pick Line Feed (this picks up the Alt+Enter separator) (note I've put 100 columns to split, this allows for 100 items to be entered in each cell, there are better but more complex ways of doing this) 4. Right Click on Types of Uniform and Unpivot Other Columns 5. Right Click on and Remove the Attribute Column 6. Right Click on the Types of Uniform and select Group By... 7. New Column name = "Total Sold" Operation = Sum Column = Value 8. Click Home > Close and Load To.... then select table and load the data into Excel Done! Simply Right Click and Refresh the green table whenever required Ideally we'd avoid all this and explain to the user why using Alt+Enter, in this case, is not a particularly good idea. The aim of this unusual example is just to get you thinking that Get & Transform may well be able to help you ways you never thought were possible.
