• Zur Hauptnavigation springen
  • Zum Inhalt springen
  • Zur Seitenspalte springen
  • Zur Fußzeile springen

THE SELF-SERVICE-BI BLOG

Wir lieben Microsoft Power BI

  • Videokurse
    • Coming soon!
  • Live Online Power BI-Schulungen
  • Beratung
    • Was ich für Dich tun kann
    • Showcases
  • Kunden
  • BLOG
    • Business Topics
    • Technical Topics (english)
    • Tools
  • Podcast
  • Kontakt
  • Über

Technical Topics (english) / 9. August 2019

Pitfalls with Table.ColumnsOfType

I recently came across a behavior of the Table.ColumnsOfType() function that I want to share with you today. Since this post talks about ascribed types in M, I recommend that you read this post first if you are not familiar with these topics yet.

Preface

The function Table.ColumnsOfType() allows you to retrieve a list of columns that match specified types (second argument) from a given table (first argument). This can be very helpful, for example, if you want to select all text columns of a given table.

The syntax of the function is as follows: Table.ColumnsOfType(table as table, listOfTypes as list) as list.

So let’s see how it works with an example.

The starting point

My starting point was the following script, which…

  1. Creates a table ‚Source‘ and ascribes types to each of the columns,
  2. Returns a list of all column names from columns of type text,
  3. Selects those columns from table ‚Source‘

The following screenshot shows the script and corresponding views in Power Query:

Using Table.ColumnsOfTypes to select columns of type text from a table with ascribed types, Power Query, Power BI
Using Table.ColumnsOfTypes to select columns of type text from a table with ascribed types

Everything works as expected. And now let’s try to reproduce this without ascribing the types, but using the UI…

Using Table.ColumnsOfTypes to select columns of type text from a table with defined types using the UI, Power Query, Power BI Desktop
Using Table.ColumnsOfTypes to select columns of type text from a table with defined types using the UI

In the example shown above, I use exactely the same table as in the previous example for step Source. Both tables are defined in M, so they are not imported from any external data source. After that I use the UI to change the data types of columns A and B, which leads to step #"Changed Type". This creates the corresponding M code and you can see that it assigns the type number to column „A“ and the type text to column „B“, as in the previous example. Step ColumnsToSelect and Output are exactly the same as in the previous example. But as you can see from the screenshots to the right of the M code, the list that filters for columns of type text(step ColumnsToSelect) returns no value. This means that no column can be selected in step Output. What’s going on here?

What happened here?

To bring a little more light into the darkness, I apply the function Table.Schema() to both examples after the types have been assigned. For the first working solution, Table Schema() returns the following:

Table.Schema() for the working solution, Power Query, Power BI
Table.Schema() for the working solution

For the variant that did not return a result for me, Table.Schema() returns this result:

Table.Schema() for the not working solution, Power Query, Power BI
Table.Schema() for the not working solution

I was using Table.Schema() immediately when I encountered this problem. However, I didn’t notice the difference with IsNullable. It was the Power Query Dev team who made me aware of this difference. Many thanks for that. But why is it that when using the UI for the type declaration all columns are nullable? Very simple: The used function Table.TransformColumnTypes() – although the M-Code does not contain anything about ’nullable‘ – automatically returns nullable datatypes. This is rarely a problem. But in my example, which uses the function Table.ColumnsOfType(), it becomes one, because this function needs an ‚exact match‘ for the type to be selected.

So what’s the solution?

Solution

The thing is that nullability matters for Table.ColumnsOfType(). And if the function Table.TransformColumnTypes() always returns a nullable data type, then the function Table.ColumnsOfType() must also search for such a data type. The solution is as follows:

Nullability matters, Power Query, Power BI
Nullability matters

A quite simple solution, that has been driving me crazy for a while.

Conclusion

  1. Within the function Table.ColumnsOfType() it must be exactly defined, which type the columns of the table have, in order to come to a result. This also includes nullability.
  2. From the pure M code it cannot be read whether the generated type is nullable or not. The Table.Schema() function is very helpful here.
  3. Table.TransformColumnTypes() is not the only possible cause for ‚unwanted‘ nullability. If you took a closer look at my first example with the ascribed types in this post, then the column „C“ of type any was converted to nullable any as well. This was only made clear by Table.Schema().

Greetings from Germany,

Lars

Lars Schreiber

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: M, Power Query, Type system

Leser-Interaktionen

Kommentare

  1. David Whitney meint

    23. April 2024 um 7:01 pm

    Hervorragend. Saved my ass.

  2. Erivaldo Lopes meint

    22. Februar 2024 um 9:54 pm

    Hi Lars,
    Thanks for this post. It has helped me with my table transformations and pin-point errors.
    By the way, is there a difference between nullable types and non-nullable types?

  3. Ron meint

    2. November 2023 um 1:12 pm

    Thanks for your insight. However, I have a table with mixed „type number“ and „type any“. „Table.ColumnsOfType(#“Changed Type“,{type nullable any})“ (or „Table.ColumnsOfType(#“Changed Type“,{type any})“) return ALL of the column names. Is there any simple way of returning ONLY those columns of „type any“? (Other than removing the other columns from the generated list)?

  4. Justin Hylton meint

    30. Dezember 2021 um 3:48 pm

    Thank you for this article, I was so confused why Table.ColumnsOfType was returning weird results for me.

    • Lars Schreiber meint

      5. Januar 2022 um 9:04 pm

      Happy I could help 🙂

  5. Mark Biegert meint

    10. Februar 2020 um 6:16 pm

    Very helpful. Great presentation. Thanks.

    • Lars Schreiber meint

      11. Februar 2020 um 7:50 am

      Hi Mark,

      thanks for your feedback. Happy, that it helped you out 🙂

      Cheers,
      Lars

  6. Curt Hagenlocher meint

    9. August 2019 um 4:27 pm

    „type nullable any“ and „type any“ are the same value. The complement to „any“ is „anynonnull“ — that is, „type nullable anynonnull“ is the same as type „any“.

    • Lars Schreiber meint

      10. August 2019 um 5:17 am

      Why didn’t I think of that on my own? * irony off… Curt, as always, thanks a lot for the clarification

Seitenspalte

WEBSITE DURCHSUCHEN

MELDE DICH FÜR DIE POWER BI USER GROUP HAMBURG AN

Trage Deine E-Mailadresse ein, um für kommende Treffen der PUG Hamburg eingeladen zu werden. Zudem erhältst Du Zugriff auf die Materialien der vergangenen Treffen.

Footer

Kontakt

THE SELF-SERVICE-BI BLOG
Mail: lars@ssbi-blog.de

Rechtliches

  • Impressum
  • Datenschutz

Ü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.

Social

Folge mir...

Copyright © 2025 · Digital Pro on Genesis Framework