Forum Discussion
Has anyone been able to reliably use Table.ColumnsofType() in Power Query?
it seems it only recognises the primitive types as per language specification
https://msdn.microsoft.com/en-us/query-bi/m/power-query-m-language-specification
if you look in #shared, and filter for .Type, as below
let
Source = #shared,
#"Converted to Table" = Record.ToTable(Source),
#"Filtered Rows" = Table.SelectRows(#"Converted to Table", each Text.EndsWith([Name], ".Type"))
in
#"Filtered Rows"you can see that all the Int64, Currency etc. all have type number, which I assume is what the function is getting
Interesting. That is helpful as to when it works why it works the way it does. The documentation needs to specify this IMHO. I would try the "Contribute" feature to add this, except I cannot get it to work consistently, as in the empty list in my example above.
For anyone else who may have hit this page, see this blog for insight. The solutions is to expand type list as such
Table.ColumnsOfType(Patients1, {type nullable text})Hi edhans,
Have you solved your problem?
If you have solved, please accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.
If you still need help, please feel free to ask.
Best Regards,
Cherry
I Cherry. No, it isn't resolved. I cannot get it to work reliably as noted above. Sometimes it pulls broad data (like integers, numbers, percents) when i use type number, which is ok I suppose (the documentation isn't clear on this at all), and other times, it pulls nothing.
Lars Schreiber's investigation, solution and explanation from blog link above worked for me!
hansei wrote:For anyone else who may have hit this page, see this blog for insight. The solutions is to expand type list as such
Table.ColumnsOfType(Patients1, {type nullable text})Thanks LarsSchreiber
