Recently Power Query’s replacement function greeted me with some unexpected results: I applied the same steps but sometimes replacements of blanks with nothing did what it should and sometimes not.
What was going on?:
Starting with a table with 2 columns like this:
The results came out as expected, blanks were removed by nothing, so all gaps closed:
Then I added the Values-column like this and applied the same steps as above:
Edit 7th August 2016: You’ll find an easier “OneInAll”-approach in the PowerBI forum here. Thanks to David Moss for reporting an issue with the solution below.
PBI-file with new query: NewAllInOneCode.zip ,
This week we got a new Sankey diagram. It has some advantages when visualizing dependencies between items/nodes, one being not to cut the labels. Time to share a tool that I use a lot when working with complex M-queries (in Power Query or Power BI). It shows how the queries are connected with each other:
Left stand the queries who fetch the source data, parameters and/or user defined filters, then you see how they get transformed until to the right you see the queries that deliver your end-results. Especially when working with codes that has been written by someone else I find it very useful to get a quick oversight of what’s happening – as an excellent complement to the documentations 🙂
You can also move the nodes around to improve readability:
Edit 03 Aug 2016: With the July 2016 release you can now run your R-scripts directly in the query-editor (so no row-limit any more!). No need to go via the visuals. But this will limit you to export datasets from the query-editor, so no DAX.
Edit 22 Jan 2016: Currently this method is limited to 150k rows! It will drop all others without warning!
Edit 25 Jan 2016: 150k rows is the limit on all R-scripts at the moment (link). A warning sign will be shown on the image. Will create a new blogpost if this changes – so stay tuned (by following this blog, Twitter or LinkedIn)
With the December release, Microsoft enabled Power BI’s content to be fetched from the R-feature. So instead of plotting this data to the screen, could we just export it to a file?
Yes we can:
write.table(trim(dataset), file=”your filepath & filename.txt”, sep = “\t”, row.names = FALSE);
write.table has a lot of options, which are explained here. I’ve only used a few here: Exactly defining the location where the output-file shall be stored (file=), setting tab as delimiter (sep=”\t”) and skipping row names, which would be an index in our example here.
In addition to that, I had to get rid of trailing whitespaces that somehow sneaked into the table (trim(dataset)). Therefore the gdata-package is needed. So the full R-code looks like this:
require(gdata)
write.table(trim(dataset), file=”your filepath & filename.txt”, sep = “\t”, row.names = FALSE)
plot(dataset);
Here you have to make sure to turn the slashes in the filepath: So if your path looks like this: C:\Users\Imke\Desktop\Rfile.txt you need to write it like this: C:/Users/Imke/Desktop/Rfile.txt
NEW: You find a function for this export here: https://github.com/ImkeF/M/blob/master/LibraryR/Table.ExportToCsv.pq , it swaps the slashed automatically.
Let’s take this as a start that will probably fulfill many users need to quickly export data from Power BI to simple text files. Further use cases I see are:
This DAX-VizArt-Wizard vizualizes dependencies between your DAX measures, shows the definition of all related measures and shows differences between the measures of 2 models/versions. This works for Power Pivot, Power BI and for Analysis Services Tabular (SSAS).
In the Power BI-Version you’ll see them in the Sankey-chart like this:
If select measures/nodes, all direct connections will be highlighted:
In the second version, all indirect connections will be highlighted as well & the selected measure definitions will be shown.
Reading Jen Stirrup’s great article about the learning path for SQL Server 2016 and R I ended up learning my first R pieces from this wonderful post that she has referenced. There Tony Ojeda beautifully describes how basic Excel-concepts are translated into R.
Well – I cannot speak of so much personal experience here – but these R-codes came easy as nothing before to me. Apart from Tony’s first class didactics, could this be due to what I’ve learned in Power Query over the last year?
I’ve added the comparison of Excel and R by the M-code from Power Query – putting it in the middle:
Do you find the similarities as striking as I do?
Parameter tables in Excel are normally set up like this: In order to retrieve the currency for example, you could write this: = Parameters[Value]{2} This returns the content of the “Value”-column of the query named “Parameter” that sits in the 3rd row (! Power Query starts to count at zero): EUR. Which is not bad, but also not … Read more