{"id":1098,"date":"2021-01-13T10:24:32","date_gmt":"2021-01-13T16:24:32","guid":{"rendered":"http:\/\/ninmonkeys.com\/blog\/?p=1098"},"modified":"2021-01-13T10:25:39","modified_gmt":"2021-01-13T16:25:39","slug":"preserving-types-when-using-add-custom-column-in-power-query","status":"publish","type":"post","link":"https:\/\/ninmonkeys.com\/blog\/2021\/01\/13\/preserving-types-when-using-add-custom-column-in-power-query\/","title":{"rendered":"Preserving Types When using &#8220;Add Custom Column&#8221; in Power Query"},"content":{"rendered":"\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" src=\"https:\/\/ninmonkeys.com\/blog\/wp-content\/uploads\/2021\/01\/image-6.png\" alt=\"\" class=\"wp-image-1099\" width=\"613\" height=\"204\" srcset=\"https:\/\/ninmonkeys.com\/blog\/wp-content\/uploads\/2021\/01\/image-6.png 627w, https:\/\/ninmonkeys.com\/blog\/wp-content\/uploads\/2021\/01\/image-6-300x100.png 300w\" sizes=\"(max-width: 613px) 100vw, 613px\" \/><figcaption>The default UI sets your column to <code class=\"\" data-line=\"\">type any<\/code>.<\/figcaption><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" width=\"658\" height=\"206\" src=\"https:\/\/ninmonkeys.com\/blog\/wp-content\/uploads\/2021\/01\/image-7.png\" alt=\"\" class=\"wp-image-1100\" srcset=\"https:\/\/ninmonkeys.com\/blog\/wp-content\/uploads\/2021\/01\/image-7.png 658w, https:\/\/ninmonkeys.com\/blog\/wp-content\/uploads\/2021\/01\/image-7-300x94.png 300w\" sizes=\"(max-width: 658px) 100vw, 658px\" \/><figcaption>You can use the optional argument of <code class=\"\" data-line=\"\">Table.AddColumn<\/code> to set it to <code class=\"\" data-line=\"\">number<\/code><\/figcaption><\/figure>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" width=\"769\" height=\"225\" src=\"https:\/\/ninmonkeys.com\/blog\/wp-content\/uploads\/2021\/01\/image-8.png\" alt=\"\" class=\"wp-image-1101\" srcset=\"https:\/\/ninmonkeys.com\/blog\/wp-content\/uploads\/2021\/01\/image-8.png 769w, https:\/\/ninmonkeys.com\/blog\/wp-content\/uploads\/2021\/01\/image-8-300x88.png 300w\" sizes=\"(max-width: 769px) 100vw, 769px\" \/><figcaption>Or you can declare your function&#8217;s return type<\/figcaption><\/figure>\n\n\n\n<h2>Why doesn&#8217;t the original <code class=\"\" data-line=\"\">[Num] * 2 <\/code> work?<\/h2>\n\n\n\n<p>Powerquery does not know what type will be returned by your function. That&#8217;s because <code class=\"\" data-line=\"\">each<\/code> is by definition a function that returns <code class=\"\" data-line=\"\">type any<\/code><\/p>\n\n\n\n<h2>See More<\/h2>\n\n\n\n<ul><li>see the <a href=\"https:\/\/docs.microsoft.com\/en-us\/powerquery-m\/m-spec-consolidated-grammar#each-expression\"><code class=\"\" data-line=\"\">each<\/code> expression in the Lexical Grammar<\/a><\/li><li>Docs on <a href=\"https:\/\/docs.microsoft.com\/en-us\/powerquery-m\/m-spec-functions#simplified-declarations\">Power Query <code class=\"\" data-line=\"\">each<\/code> functions<\/a><\/li><\/ul>\n","protected":false},"excerpt":{"rendered":"<p>Why doesn&#8217;t the original [Num] * 2 work? Powerquery does not know what type will be returned by your function. That&#8217;s because each is by definition a function that returns type any See More see the each expression in the Lexical Grammar Docs on Power Query each functions<\/p>\n","protected":false},"author":1,"featured_media":1102,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[13,9,40],"tags":[6,7,22],"_links":{"self":[{"href":"https:\/\/ninmonkeys.com\/blog\/wp-json\/wp\/v2\/posts\/1098"}],"collection":[{"href":"https:\/\/ninmonkeys.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ninmonkeys.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ninmonkeys.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/ninmonkeys.com\/blog\/wp-json\/wp\/v2\/comments?post=1098"}],"version-history":[{"count":8,"href":"https:\/\/ninmonkeys.com\/blog\/wp-json\/wp\/v2\/posts\/1098\/revisions"}],"predecessor-version":[{"id":1113,"href":"https:\/\/ninmonkeys.com\/blog\/wp-json\/wp\/v2\/posts\/1098\/revisions\/1113"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/ninmonkeys.com\/blog\/wp-json\/wp\/v2\/media\/1102"}],"wp:attachment":[{"href":"https:\/\/ninmonkeys.com\/blog\/wp-json\/wp\/v2\/media?parent=1098"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ninmonkeys.com\/blog\/wp-json\/wp\/v2\/categories?post=1098"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ninmonkeys.com\/blog\/wp-json\/wp\/v2\/tags?post=1098"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}