A recent attendee at a webinar posed the question, can you change the Close & Load setting on an existing query? Here is the answer.
Category Archives: Power BI
Power Query feedback
Received this email after a recent Power Query session – some things may take a few goes to understand, that’s OK, that’s how we learn.
Free Webinar Recording – Introduction to Power Query
Feedback score 94.5% based on 91 responses
In October 2019 I ran my Introduction to Power Query webinar for free (previously it was a paid session). I want to get this information out to as many people as possible. please share this resource with colleagues and your network.
The detailed pdf manual and example file can be downloaded by using the button below. Content listed below the video.
Power Query allows you to automatically perform data cleansing routines on your data sources – no manual intervention required. Simply refresh and your data is ready to use. You can use csv files; txt files; databases and existing Excel tables as your data sources. Learn the basics, plus an advanced technique to automate data cleansing routines on your data sources.
CPD note – if you are claiming CPD for watching this recording you need to keep your own records. People who attend the live sessions receive an annual listing of attendances.
This session covers
- fixing dates so that Excel can recognise them
- formatting columns as text – retaining leading zeroes in CSV files
- deleting unwanted rows and columns from your data
- removing leading and trailing spaces
- populating blank values with zeroes
- populating blanks with entries from above
- correcting trailing minus signs
- unpivot a report – how to convert a report layout into a data table layout
- converting a MYOB report into a data table
Promoting Headers Issue in Power Query
Be careful if a first row name changes
Promoting headers in Power Query means using the first row as column headers. In Power Query this is a useful and common option. In some cases it is even automated. There is one time though when you don’t want to use it.
Power Query Fill Down Doesn’t Work
The fix is easy
Sometimes when working with CSV files in Power Query you may strike the situation where Fill Down doesn’t fill down. Don’t worry there is an easy fix.
Power Query
Its always great to receive e-mails like this. Power Query is a game changer.
Power Query and Open Excel Files
Old school solution
Power Query can easily combine data from multiple Excel files. A problem can arise if one or more of the files is open. Power Query will generate an error and the import will fail. The solution involves an old school Excel feature.
Creating a table of all combinations in Excel [Video]
Power Query solution
Let’s assume you have three state codes and four department codes and you want to create a table of all the possible 12 combinations (3 x 4). How do you do it so that it is flexible? i.e. if you add a new state or department it must be easy to update the combination table.
Filter in Power BI Data View
Filtering coming soon to the Data view in Power BI. Fingers crossed this gets done soon – patience everyone.
Dates are Easy with Power Query
Getting the date right and save time
You can create complex functions with Excel to handle dates. But it makes more sense to get your data structured correctly and then you can use simpler date formulas. Power Query allows you to fix your data so that you can use those simpler formulas.
Power Query Privacy Setting Problem
Excel 2016 solution
Privacy settings allow you to control who sees the Power Query data. There seems to be a bug that remembers your response to a dialog and this ignores any changes to the Privacy settings. Find out the VBA line of code that can fix it.
PDF and Power BI
Looks like Power BI will soon be able to extract data from tables in PDF documents.
Great to see requests being acted on.
Understanding Filter Context in DAX
An Excel-based explanation
Are you trying to get your head around Filter context in DAX? I watched a video from the sqlbi.com guys and it explained it well. I thought I could add an Excel flavour to it.
Creating a Unique Dynamic List in Excel
A Power Query technique
There are a couple of techniques to automate a unique list of items in Excel. I have covered them in previous blog posts (see links below). I thought I would describe how to use Power Query to create a dynamic unique list.
Adam Saxton - Microsoft
2017-06-12
2017-06-12
All the Power BI whitepapers in one place.
An Avo Chart
I wonder if we can get one of these on Power BI?
Saw this on the website below and liked it – I also like Avocados.
Avocado article on the ABC site
David Iseminger - Microsoft
2017-03-29
2017-03-29
If you are into statistics then you probably already know about the R language – but if you don’t it may be worth looking at.
It is open source code that is built to handle statistics and big data. It has some limitations when used with Power BI but it can be used on its own.
This article looks at using visuals created with R in Power BI.
DAX is the function and query language of PowerPivot and PowerBI. It has been around for a while, but it didn’t really have many editing tools until DAX Studio came around.
DAX Studio has been created by the SQLBI.com guys – the legends in PowerBI.
I saw a demo today at the Perth Modern Excel User Group at Microsoft Perth – its great.
Whilst it is a powerful editor, it is not just a code editor.
There are lots of options to allow you to optimise your DAX code. Lots of output options as well.
You can test and see how long it takes to run your DAX commands.
If you regularly use DAX in Power BI or PowerPivot then DAX Studio is a must have.
Chris Webb
2017-04-11
2017-04-11
Chris Webb’s BI website is a great resource on Power BI, DAX and Power Query and Power Pivot
This post shows how to hack DAX to display characters. Opens up some new possibilities.
A listing of all the DAX functions with their syntax.
DAX functions are used in Power BI and PowerPivot for Excel.
It is a reference site, not a training site.
It has been created by Marco Russo and Alberto Ferrari – legends in Power BI and all things DAX.
Link to DAX Guide