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