{"id":1317,"date":"2023-02-22T12:07:06","date_gmt":"2023-02-22T10:07:06","guid":{"rendered":"https:\/\/www.flip-design.de\/?p=1317"},"modified":"2023-02-22T12:07:06","modified_gmt":"2023-02-22T10:07:06","slug":"extract-numbers-from-strings-by-using-powerquery","status":"publish","type":"post","link":"https:\/\/www.flip-design.de\/?p=1317","title":{"rendered":"Extract numbers from strings by using PowerQuery"},"content":{"rendered":"\n<p>A customer told me, that he have a system with polls which collects numbers and strings as an answer. The result looks like these:<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><a href=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2023\/02\/image.png\"><img decoding=\"async\" loading=\"lazy\" width=\"586\" height=\"425\" src=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2023\/02\/image.png\" alt=\"\" class=\"wp-image-1318\" srcset=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2023\/02\/image.png 586w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2023\/02\/image-300x218.png 300w\" sizes=\"(max-width: 586px) 100vw, 586px\" \/><\/a><\/figure>\n\n\n\n<p>Now, he wants to summarize the numbers and also to show the written answers. Next, I added a new custom column which extracts numbers from the column.<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><a href=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2023\/02\/image-1.png\"><img decoding=\"async\" loading=\"lazy\" width=\"945\" height=\"592\" src=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2023\/02\/image-1.png\" alt=\"\" class=\"wp-image-1319\" srcset=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2023\/02\/image-1.png 945w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2023\/02\/image-1-300x188.png 300w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2023\/02\/image-1-768x481.png 768w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2023\/02\/image-1-325x205.png 325w\" sizes=\"(max-width: 945px) 100vw, 945px\" \/><\/a><\/figure>\n\n\n\n<p>Now, I got a new column with the numbers. You need to format the source column as a whole number. If you want to keep the text, you need to duplicate the column. For the values of type text, you will receive an error. You can replace the errors with NULL.<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><a href=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2023\/02\/image-2.png\"><img decoding=\"async\" loading=\"lazy\" width=\"945\" height=\"453\" src=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2023\/02\/image-2.png\" alt=\"\" class=\"wp-image-1320\" srcset=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2023\/02\/image-2.png 945w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2023\/02\/image-2-300x144.png 300w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2023\/02\/image-2-768x368.png 768w\" sizes=\"(max-width: 945px) 100vw, 945px\" \/><\/a><\/figure>\n\n\n\n<p>At the end, you can analyze the values:<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><a href=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2023\/02\/image-3.png\"><img decoding=\"async\" loading=\"lazy\" width=\"945\" height=\"555\" src=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2023\/02\/image-3.png\" alt=\"\" class=\"wp-image-1321\" srcset=\"https:\/\/www.flip-design.de\/wp-content\/uploads\/2023\/02\/image-3.png 945w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2023\/02\/image-3-300x176.png 300w, https:\/\/www.flip-design.de\/wp-content\/uploads\/2023\/02\/image-3-768x451.png 768w\" sizes=\"(max-width: 945px) 100vw, 945px\" \/><\/a><\/figure>\n","protected":false},"excerpt":{"rendered":"<p>A customer told me, that he have a system with polls which collects numbers and strings as an answer. The result looks like these: Now, he wants to summarize the numbers and also to show the written answers. Next, I &hellip; <a href=\"https:\/\/www.flip-design.de\/?p=1317\">Continue reading <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0},"categories":[1],"tags":[],"_links":{"self":[{"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/posts\/1317"}],"collection":[{"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1317"}],"version-history":[{"count":1,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/posts\/1317\/revisions"}],"predecessor-version":[{"id":1322,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/posts\/1317\/revisions\/1322"}],"wp:attachment":[{"href":"https:\/\/www.flip-design.de\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1317"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1317"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1317"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}