INDEX MATCH the easy way
I've created a short video on how to use a trick to create INDEX MATCH formula quicker. Also, my suggestion for a revised version of INDEX MATCH is now at number 6 (as at 22nd Sep 2016) in the most voted for improvements to Excel. https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/14049132-make-a-simple-safer-version-of-vlookup-and-index-m
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.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!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!
Intro to Get & Transform
We just released 3 great free hosted labs for Excel. Take a look at the Intro to Get & Transform lab This lab is intended to serve as an introduction to the Get & Transform feature area in Excel 2016. The lab will walk through edit and transform the data you are importing, merge multiple queries together, and creating simple PivotTables to view the data in Excel. Hope you like it
Complicated formula to search for certain data
Hi guys, I have a big problem, I'm on internsip and I've gotten a task to develop an excel file which will search the data base and take out the names of companys that stopped making transactions. For instance if one company made a transaction in 1,2,3 month and stopped in 4th I would like the excel to automaticlly show up only these companies. I tried to do it by pivot tables but it's imposible to search indyvidually in so much data, and my task was to automatize that. My other idea was to develop an complicated IF function, for instance IF B2>=1 than true and B2<1 than false and include every of 6 months in formula so in the end we might get a diffrent score(either 1 or 0) if you get 1 it means that company made transaction every month and if we get 0 company stopped making transaction at some point. I would appreciate any help, any suggestions! Lookin a likes tables included in photos bellow. Second one if how my database look like, and first is copyied pivot tables to normal sheet that executes formulas. RegardsIntro to Analyzing Data in Excel
In the last lab of 3 we will look at Intro to Analyzing Data in Excel This lab is intended as an introduction to Analyzing Data in Excel. In the previous labs we have discussed getting data into Excel and the Excel Data Model. In this lab we will use a more advanced data model that is completed for us. This lab will focus on creation and usage of PivotCharts and Slicers, to create a dashboard in Excel. Take the lab now!Power Pivot Introduction video
A demonstration of how to get started with Power Pivot This is part 3 of a series: The first video shows how to import 52 CSV files using Get & Transform (Power Query). The second video shows how to shape that data and pull it into a standard Pivot Table This third video then shows how to join "lookup" tables together using Power Pivot and display data on a Fiscal Year rather than Calendar year basis
