en de en de de en de en id de en nl en en en en en de de en de de de de de en en en en nb en en en en en en en en en en en en en en en en en en en en en en en en en en en en en en de de de de de de de de de de de de sv fa it en de de de en nl de en en en en en en de en nds de it de en de en de en
A couple of  weeks ago I could finally publish my post about tables in M – how, when and why. Under „Other special table functions“ I mentioned the function Table.Profile() as a function to get meta information about tables. During my research I noticed that this function accepts a second optional parameter which is not documented anywhere. Also other posts about this function, which colleagues from the community had already written, did not mention this parameter (at least none I could find). This made me curious, so I reached out to the Power Query dev team.
The syntax of the function is as follows: Table.Profile(table as table, optional additionalAggregates as nullable list) as table. The first mandatory parameter is the table whose profile is to be created. The second parameter is a puzzle. Let’s take a closer look at this one.
This argument is expected as a list of lists, like this: {{},{}}. Each of these inner lists represents a new calculated profile column and consists of three items:
item: Output column name as text
item: typecheck function as function
item: aggregation function as function
Let’s take a detailed look.
This is just the name of the new profile column, which of course should reflect the content of the column.
The typecheck function is expected to be something like each Type.Is(_, type any). While the keyword „each“‚ refers to the current record in the profiling table, the underscore „_“ refers not – as you might expect – to the current record, but to the column of the source table, whose name can be found in the current record in the first column „Column“. So the underscore „_“ doesn’t return a record, but a list. This information will also be important for the third item of the parameter additionalAggregates: the aggregation function.
If the second parameter – the typecheck function – returns true, then the aggregation function kicks in. Otherwise the output of the aggregation function will be null.
So how could examples of the aggregation function look like?
Aggregation function – example #1
= Table.Profile(Table.FromRows({{1, 2}, {3, null}}, type table [A=any, B=number]), {{"New profile column", each Type.Is(_, type number), each List.Average(_)}})
What happens in the profile table?
Column A: This column is of type any. My function is explicitly looking for type number (Type.Is(_, type number)), so the function List.Average() is not used for column A. The value null is returned accordingly.
Column B: This column is of type number. My function is explicitly looking for type number (Type.Is(_, type number)), so the function List.Average() can kick in. The value 2 is returned as the average of 2 and null is 2.
Aggregation function – example #2
= Table.Profile(Table.FromRows({{"ABC", 2}, {"C", 3}}, type table [A=text, B=any]), {{"AverageCharactersCount", each Type.Is(_, type text), each List.Average( List.Transform(_, each Text.Length(_))) }})
What happens in the profile table?
Column A: This column is of type text. My function is explicitly looking for type text (Type.Is_, type text), so the function List.Average( List.Transform(_, each Text.Length(_))) is used for column A. The returned value is 2 as the average text length of „ABC“ and „C“ is 2.
Column B: This column is of type any. My function is explicitly looking for type text (Type.Is_, type text), so the function List.Average( List.Transform(_, each Text.Length(_))) doesn’t kick in, but returns null.
When using the UI to assert types
When you change a data type using the UI, the M function Table.TransformColumnTypes() is used behind the scenes to do that job. What you should always keep in mind when using this function is, that it always returns a nullable data type. This is something I forgot, even if it was a central message of this of my own recent posts. Why is this important in view of the last post? Take a look at the following examples:
In the first screenshot I use ascribing types and my custom column to Table.Profile() just works.
In the second screenshot I use the UI/ Table.TransformColumnTypes() to assert the type and my function returns null, where I expected the value 2. Why does that happen. As I said, in my example Table.TransformColumnTypes() returns a nullable text type and Type.Is(type nullable text, type text) = false. This is why I get back null.
To fix this behavior, I have to check for type nullable text, instead of type text:
The moment you do it right, it’s running. 🙂
Lars ist Berater, Entwickler und Trainer für Microsoft Power BI. Er ist zertifizierter Power BI-Experte und Microsoft Trainer. Für sein Engagement in der internationalen Community wurde Lars seit 2017 jährlich durch Microsoft der MVP-Award verliehen. Lies hier mehr…
Kategorie: Technical Topics (english) Stichworte: Excel, M, Power BI, Power Query
Lars Schreiber bei So passt sich Deine Kalendertabelle automatisch an neue Jahre an
Nico Thormann bei So passt sich Deine Kalendertabelle automatisch an neue Jahre an
RaiSta bei Get your own Power Query Editor using Notepad++
Lars Schreiber bei Tabellenüberschriften massenhaft und dynamisch umbenennen mit Power Query
Daniel bei Tabellenüberschriften massenhaft und dynamisch umbenennen mit Power Query
Schreibe einen Kommentar Antworten abbrechen
Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert
Lists, Records and Tables in M - how, when and why
Writing Documentation for custom M functions
The Environment concept in M for Power Query in Excel and Power BI Desktop
Creating asymmetric Pivots and control them by Slicer
Über THE SELF-SERVICE-BI BLOG
Ich bin ein freiberuflicher Power BI-Berater, -Entwickler und -Trainer und wurde von Microsoft mehrfach mit dem MVP Award ausgezeichnet. Ich arbeite mit einem kompetenten Netzwerk aus freiberuflichen und ambitionierten Kollegen zusammen. Erfahre hier mehr.