Power Query performance

Power Query is quite fast and the underlying data model engine in PowerBI and Excel has excellent performance. Pragmatic works has a nice pdf with tips. The single best thing to do in favour of performance is to remove any unneeded fields from the core queries. This means to only bring in those fields that are needed, eliminate others from the original query so that they are not brought over in the first place.

Query data from websites into Excel or PowerBI

Power Query works great to extract data from websites. For example, I have a Joomla site that has a list of articles in a table. I wanted to summarize article hits per year.

Using Excel and Power Query:

  1. Fresh Excel sheet – Data->New Query  Put in the path to the website, pick the correct table and load the data.
  2. Take time to set the correct data type for each column. This helps on later stages. Especially ensure that you pick whole numbers for columns that have whole numbers, datetype for such columns etc. Also, remove any unwanted columns etc.
  3. Save and close the query – and your data will be available in Excel. Now, create a PivotTable, join with other data etc.

This process would be very similar for PowerBI.

 

IT access to a private Analyzer layout

At AXnorth we sell and support the AXtend Analyzer. One of the great features of the Analyzer is how users can create their own private layouts, their own way of seeing the data.

If the need arises that you as an IT needs to look at these layouts here is how you make yourself the owner of a given layout (assuming that you have edit access to the database).

Layoutname in question: PrivateLayout1
AD name of IT person: domain\it1
AD name of the original creator of layout: domain\un1

Here are the steps:
1. Use this query to retrieve the needed information about the layout:
select * from na.FormLayout_v2 f
where f.LayoutName like ‘%PrivateLayout1%’

Make a copy of the AD name of the original creator of the layout so you can revert your changes back later. Also, make sure you have the unique name of the layout ready to use for the following update statement. You want to make sure only to update no more than one layout.

2. Run a sql update to make yourself the owner of the layout:
update na.FormLayout_v2
set UserName = ‘domain\it1’
where LayoutName = ‘PrivateLayout1’

3. Now you can restart/open Analyzer and open the layout that you previously did not have access to.

4. Remember to revert the changes in #2 once you are done testing/working with the layout in Analyzer.

Remove duplicates from string

Here is how to remove duplicates from a concatenated string and return as a string. Notice, this is done by creating a function (RemoveDupl) which is then referenced.

To transform a column in a table using this function:

#”DuplRemoved” = Table.TransformColumns(#”Removed Columns1″,{{“MonthColumnName”, RemoveDupl }})

Power Query group by one column and concatenate string values of another column

I needed an example of how to group by one column and concatenate string values of another column.

Here is what this ended up like:
—
let
Source = #table({“Product”, “Customer”},{{ “Apples”,”Chris”},{“Apples”,”Fred”} ,{“Oranges”,”Chris”}}),
myCombine = Combiner.CombineTextByDelimiter(“;”),
#”Grouped Rows” = Table.Group(Source, {“Product”}, {{“Test”, each myCombine ([Customer]), type text}})
in
#”Grouped Rows”
—

Power Query (M) union between two tables

I have recently been discovering the strength of Power Query (nick name M). It is a programming language that Microsoft has been introducing into Excel Power Pivot, Power BI and more.

Having a full blown programming language makes these tools all the more appealing. Here is a test example on how to union two tables together:
—
let
TableA = #table({“Name”, “Age”}, {{“John”,23},{“Mary”,25}}),
TableB = #table({“Name”, “Age”}, {{“Smith”,33},{“Ulrich”,35}}),

TableC = Table.Combine({TableA, TableB})
in
TableC
—