Category: Power Query

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.

 

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
—