{"id":2894,"date":"2020-03-04T13:52:46","date_gmt":"2020-03-04T12:52:46","guid":{"rendered":"https:\/\/thebipower.fr\/?p=2894"},"modified":"2021-03-25T15:47:27","modified_gmt":"2021-03-25T14:47:27","slug":"get-username-function-in-power-query","status":"publish","type":"post","link":"https:\/\/thebipower.fr\/index.php\/2020\/03\/04\/get-username-function-in-power-query\/","title":{"rendered":"Get username function in Power Query"},"content":{"rendered":"\n<p>Get username function in Power Query with few lines of M code. For the moment, there is no native way to get the Windows username in M language. The username might be useful to filter a table or to get a file path in the &#8216;Documents&#8217; folder.  <br>The following post describes 4 ways to get the username in Power Query :<br>     <a href=\"https:\/\/querypower.com\/2017\/04\/03\/4-ways-to-get-username-in-power-query\/\">https:\/\/querypower.com\/2017\/04\/03\/4-ways-to-get-username-in-power-query\/<\/a> <br>Unfortunately, the simplest method (n\u00b02) is not reliable because of NTFS delay as explained in the post.<br>So I developped a function using the same concept with some improvements.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">How to get username function in Power Query<\/h2>\n\n\n\n<ul><li>In Power Query, add a blank query <\/li><li>Then open the advanced editor and paste the code down below<\/li><li>Rename the function <\/li><\/ul>\n\n\n\n<p>As the function requires no parameter, it is straight forward to use it<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>() =&gt; \nlet\n    Source = Folder.Contents(\"C:\\Users\\\"),\n    #\"Expanded Attributes\" = Table.ExpandRecordColumn(Source, \"Attributes\", {\"Hidden\", \"Directory\", \"ChangeTime\"}, {\"Hidden\", \"Directory\", \"ChangeTime\"}),\n    #\"Filtered Directories not hidden\" = Table.SelectRows(#\"Expanded Attributes\", each (&#91;Directory] = true) and (&#91;Hidden] = false)),\n    #\"Removed Errors\" = Table.RemoveRowsWithErrors(#\"Filtered Directories not hidden\", {\"ChangeTime\"}),\n    #\"Filtered Rows\" = Table.SelectRows(#\"Removed Errors\", each (&#91;Name] &lt;&gt; \"Public\")),\n    #\"Username\" = #\"Filtered Rows\"{0}&#91;Name]\nin\n    #\"Username\"<\/code><\/pre>\n\n\n\n<p>Limitation : it works well in the desktop but not sure it will work so good in the service depending on the way you refresh the data. For example, if you refresh data with a gateway, you might get the username of the Windows session which launched the refresh.<\/p>\n\n\n\n<p>You may also have a look to Power BI assistant :    <a href=\"https:\/\/thebipower.fr\/index.php\/power-bi-assistant\/\">https:\/\/thebipower.fr\/index.php\/power-bi-assistant\/<\/a> <\/p>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n\t\t\t<div class=\"wp-block-uagb-post-carousel uagb-post-grid  uagb-post__image-position-top uagb-post__image-enabled uagb-block-b4d81b5c     uagb-post__arrow-outside uagb-slick-carousel uagb-post__items uagb-post__columns-3 is-carousel uagb-post__columns-tablet-2 uagb-post__columns-mobile-1\" data-total=\"5\" style=\"\">\n\n\t\t\t\t\t\t\t\t\t\t\t\t<article class=\"uagb-post__inner-wrap\">\t\t\t\t\t\t\t\t<div class='uagb-post__image'>\n\t\t\t\t\t\t\t\t\t<a href=\"https:\/\/thebipower.fr\/index.php\/2022\/10\/22\/custom-analyser\/\" target=\"_self\" rel=\"bookmark noopener noreferrer\"><img loading=\"lazy\" decoding=\"async\" width=\"674\" height=\"193\" src=\"https:\/\/thebipower.fr\/wp-content\/uploads\/2022\/10\/Custom-analyser.png\" class=\"attachment-large size-large\" alt=\"Custom analyser\" srcset=\"https:\/\/thebipower.fr\/wp-content\/uploads\/2022\/10\/Custom-analyser.png 674w, https:\/\/thebipower.fr\/wp-content\/uploads\/2022\/10\/Custom-analyser-300x86.png 300w\" sizes=\"(max-width: 674px) 100vw, 674px\" \/>\t\t\t\t\t<\/a>\n\t\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t\t\t<h4 class=\"uagb-post__title uagb-post__text\">\n\t\t\t\t<a href=\"https:\/\/thebipower.fr\/index.php\/2022\/10\/22\/custom-analyser\/\" target=\"_self\" rel=\"bookmark noopener noreferrer\">Custom analyser<\/a>\n\t\t\t<\/h4>\n\t\t\t\t\t\t<div class='uagb-post__text uagb-post-grid-byline'>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\t\t\t\t<div class='uagb-post__text uagb-post__excerpt'>\n\t\t\t\t\t<p>With &#8216;Custom analyser&#8217; feature, Power BI Sidetools users can use external tools built by the&#8230;\t\t\t\t<\/div>\n\t\t\t\t\t\t<div class=\"uagb-post__text uagb-post__cta wp-block-button\">\n\t\t\t\t<a class=\"wp-block-button__link uagb-text-link\" href=\"https:\/\/thebipower.fr\/index.php\/2022\/10\/22\/custom-analyser\/\" target=\"_self\" rel=\"bookmark noopener noreferrer\">Read More<\/a>\n\t\t\t<\/div>\n\t\t\t\t\t\t\t\t\t\t\t\t<\/article>\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<article class=\"uagb-post__inner-wrap\">\t\t\t\t\t\t\t\t<div class='uagb-post__image'>\n\t\t\t\t\t\t\t\t\t<a href=\"https:\/\/thebipower.fr\/index.php\/2021\/04\/05\/dax-debugger\/\" target=\"_self\" rel=\"bookmark noopener noreferrer\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"592\" src=\"https:\/\/thebipower.fr\/wp-content\/uploads\/2021\/04\/DAX-debugger-1024x592.png\" class=\"attachment-large size-large\" alt=\"DAX debugger\" srcset=\"https:\/\/thebipower.fr\/wp-content\/uploads\/2021\/04\/DAX-debugger-1024x592.png 1024w, https:\/\/thebipower.fr\/wp-content\/uploads\/2021\/04\/DAX-debugger-300x173.png 300w, https:\/\/thebipower.fr\/wp-content\/uploads\/2021\/04\/DAX-debugger-768x444.png 768w, https:\/\/thebipower.fr\/wp-content\/uploads\/2021\/04\/DAX-debugger-1320x763.png 1320w, https:\/\/thebipower.fr\/wp-content\/uploads\/2021\/04\/DAX-debugger.png 1452w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/>\t\t\t\t\t<\/a>\n\t\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t\t\t<h4 class=\"uagb-post__title uagb-post__text\">\n\t\t\t\t<a href=\"https:\/\/thebipower.fr\/index.php\/2021\/04\/05\/dax-debugger\/\" target=\"_self\" rel=\"bookmark noopener noreferrer\">DAX debugger in Power BI Sidetools<\/a>\n\t\t\t<\/h4>\n\t\t\t\t\t\t<div class='uagb-post__text uagb-post-grid-byline'>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\t\t\t\t<div class='uagb-post__text uagb-post__excerpt'>\n\t\t\t\t\t<p>DAX debugger is meant to help Power BI report developers displaying sample data from a&#8230;\t\t\t\t<\/div>\n\t\t\t\t\t\t<div class=\"uagb-post__text uagb-post__cta wp-block-button\">\n\t\t\t\t<a class=\"wp-block-button__link uagb-text-link\" href=\"https:\/\/thebipower.fr\/index.php\/2021\/04\/05\/dax-debugger\/\" target=\"_self\" rel=\"bookmark noopener noreferrer\">Read More<\/a>\n\t\t\t<\/div>\n\t\t\t\t\t\t\t\t\t\t\t\t<\/article>\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<article class=\"uagb-post__inner-wrap\">\t\t\t\t\t\t\t\t<div class='uagb-post__image'>\n\t\t\t\t\t\t\t\t\t<a href=\"https:\/\/thebipower.fr\/index.php\/2020\/12\/01\/manage-your-external-tools\/\" target=\"_self\" rel=\"bookmark noopener noreferrer\"><img loading=\"lazy\" decoding=\"async\" width=\"817\" height=\"495\" src=\"https:\/\/thebipower.fr\/wp-content\/uploads\/2020\/12\/External-tools-manager.png\" class=\"attachment-large size-large\" alt=\"External tools manager\" srcset=\"https:\/\/thebipower.fr\/wp-content\/uploads\/2020\/12\/External-tools-manager.png 817w, https:\/\/thebipower.fr\/wp-content\/uploads\/2020\/12\/External-tools-manager-300x182.png 300w, https:\/\/thebipower.fr\/wp-content\/uploads\/2020\/12\/External-tools-manager-768x465.png 768w\" sizes=\"(max-width: 817px) 100vw, 817px\" \/>\t\t\t\t\t<\/a>\n\t\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t\t\t<h4 class=\"uagb-post__title uagb-post__text\">\n\t\t\t\t<a href=\"https:\/\/thebipower.fr\/index.php\/2020\/12\/01\/manage-your-external-tools\/\" target=\"_self\" rel=\"bookmark noopener noreferrer\">Manage your external tools easily with Power BI Sidetools<\/a>\n\t\t\t<\/h4>\n\t\t\t\t\t\t<div class='uagb-post__text uagb-post-grid-byline'>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\t\t\t\t<div class='uagb-post__text uagb-post__excerpt'>\n\t\t\t\t\t<p>Since the Power BI team allowed to launch external tools from the Power BI desktop&#8230;\t\t\t\t<\/div>\n\t\t\t\t\t\t<div class=\"uagb-post__text uagb-post__cta wp-block-button\">\n\t\t\t\t<a class=\"wp-block-button__link uagb-text-link\" href=\"https:\/\/thebipower.fr\/index.php\/2020\/12\/01\/manage-your-external-tools\/\" target=\"_self\" rel=\"bookmark noopener noreferrer\">Read More<\/a>\n\t\t\t<\/div>\n\t\t\t\t\t\t\t\t\t\t\t\t<\/article>\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<article class=\"uagb-post__inner-wrap\">\t\t\t\t\t\t\t\t<div class='uagb-post__image'>\n\t\t\t\t\t\t\t\t\t<a href=\"https:\/\/thebipower.fr\/index.php\/2020\/10\/30\/easily-export-to-excel-with-power-bi-sidetools\/\" target=\"_self\" rel=\"bookmark noopener noreferrer\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"414\" src=\"https:\/\/thebipower.fr\/wp-content\/uploads\/2020\/10\/Export-to-Excel-1024x414.png\" class=\"attachment-large size-large\" alt=\"Export to Excel\" srcset=\"https:\/\/thebipower.fr\/wp-content\/uploads\/2020\/10\/Export-to-Excel-1024x414.png 1024w, https:\/\/thebipower.fr\/wp-content\/uploads\/2020\/10\/Export-to-Excel-300x121.png 300w, https:\/\/thebipower.fr\/wp-content\/uploads\/2020\/10\/Export-to-Excel-768x311.png 768w, https:\/\/thebipower.fr\/wp-content\/uploads\/2020\/10\/Export-to-Excel-1536x621.png 1536w, https:\/\/thebipower.fr\/wp-content\/uploads\/2020\/10\/Export-to-Excel-1320x534.png 1320w, https:\/\/thebipower.fr\/wp-content\/uploads\/2020\/10\/Export-to-Excel.png 1854w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/>\t\t\t\t\t<\/a>\n\t\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t\t\t<h4 class=\"uagb-post__title uagb-post__text\">\n\t\t\t\t<a href=\"https:\/\/thebipower.fr\/index.php\/2020\/10\/30\/easily-export-to-excel-with-power-bi-sidetools\/\" target=\"_self\" rel=\"bookmark noopener noreferrer\">Easily export to Excel with Power BI Sidetools<\/a>\n\t\t\t<\/h4>\n\t\t\t\t\t\t<div class='uagb-post__text uagb-post-grid-byline'>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\t\t\t\t<div class='uagb-post__text uagb-post__excerpt'>\n\t\t\t\t\t<p>While working in Power BI desktop, we often need to export to Excel some data&#8230;\t\t\t\t<\/div>\n\t\t\t\t\t\t<div class=\"uagb-post__text uagb-post__cta wp-block-button\">\n\t\t\t\t<a class=\"wp-block-button__link uagb-text-link\" href=\"https:\/\/thebipower.fr\/index.php\/2020\/10\/30\/easily-export-to-excel-with-power-bi-sidetools\/\" target=\"_self\" rel=\"bookmark noopener noreferrer\">Read More<\/a>\n\t\t\t<\/div>\n\t\t\t\t\t\t\t\t\t\t\t\t<\/article>\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<article class=\"uagb-post__inner-wrap\">\t\t\t\t\t\t\t\t<div class='uagb-post__image'>\n\t\t\t\t\t\t\t\t\t<a href=\"https:\/\/thebipower.fr\/index.php\/2020\/09\/03\/powerful-text-and-file-search\/\" target=\"_self\" rel=\"bookmark noopener noreferrer\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"209\" src=\"https:\/\/thebipower.fr\/wp-content\/uploads\/2020\/09\/image-6-1024x209.png\" class=\"attachment-large size-large\" alt=\"search\" srcset=\"https:\/\/thebipower.fr\/wp-content\/uploads\/2020\/09\/image-6-1024x209.png 1024w, https:\/\/thebipower.fr\/wp-content\/uploads\/2020\/09\/image-6-300x61.png 300w, https:\/\/thebipower.fr\/wp-content\/uploads\/2020\/09\/image-6-768x156.png 768w, https:\/\/thebipower.fr\/wp-content\/uploads\/2020\/09\/image-6.png 1286w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/>\t\t\t\t\t<\/a>\n\t\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t\t\t<h4 class=\"uagb-post__title uagb-post__text\">\n\t\t\t\t<a href=\"https:\/\/thebipower.fr\/index.php\/2020\/09\/03\/powerful-text-and-file-search\/\" target=\"_self\" rel=\"bookmark noopener noreferrer\">Powerful text and file search in Visual Studio Code<\/a>\n\t\t\t<\/h4>\n\t\t\t\t\t\t<div class='uagb-post__text uagb-post-grid-byline'>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\t\t\t\t<div class='uagb-post__text uagb-post__excerpt'>\n\t\t\t\t\t<p>As Power BI Sidetools creates files from your report, you are able to search through&#8230;\t\t\t\t<\/div>\n\t\t\t\t\t\t<div class=\"uagb-post__text uagb-post__cta wp-block-button\">\n\t\t\t\t<a class=\"wp-block-button__link uagb-text-link\" href=\"https:\/\/thebipower.fr\/index.php\/2020\/09\/03\/powerful-text-and-file-search\/\" target=\"_self\" rel=\"bookmark noopener noreferrer\">Read More<\/a>\n\t\t\t<\/div>\n\t\t\t\t\t\t\t\t\t\t\t\t<\/article>\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<article class=\"uagb-post__inner-wrap\">\t\t\t\t\t\t\t\t<div class='uagb-post__image'>\n\t\t\t\t\t\t\t\t\t<a href=\"https:\/\/thebipower.fr\/index.php\/2020\/09\/02\/compare-dax-and-m-formulas-in-visual-studio-code\/\" target=\"_self\" rel=\"bookmark noopener noreferrer\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"294\" src=\"https:\/\/thebipower.fr\/wp-content\/uploads\/2020\/09\/image-5-1024x294.png\" class=\"attachment-large size-large\" alt=\"Visual Studio Code\" srcset=\"https:\/\/thebipower.fr\/wp-content\/uploads\/2020\/09\/image-5-1024x294.png 1024w, https:\/\/thebipower.fr\/wp-content\/uploads\/2020\/09\/image-5-300x86.png 300w, https:\/\/thebipower.fr\/wp-content\/uploads\/2020\/09\/image-5-768x220.png 768w, https:\/\/thebipower.fr\/wp-content\/uploads\/2020\/09\/image-5.png 1269w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/>\t\t\t\t\t<\/a>\n\t\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t\t\t<h4 class=\"uagb-post__title uagb-post__text\">\n\t\t\t\t<a href=\"https:\/\/thebipower.fr\/index.php\/2020\/09\/02\/compare-dax-and-m-formulas-in-visual-studio-code\/\" target=\"_self\" rel=\"bookmark noopener noreferrer\">Compare DAX and M formulas in Visual Studio Code<\/a>\n\t\t\t<\/h4>\n\t\t\t\t\t\t<div class='uagb-post__text uagb-post-grid-byline'>\n\t\t\t\t\t\t\t<\/div>\n\t\t\t\t\t\t\t<div class='uagb-post__text uagb-post__excerpt'>\n\t\t\t\t\t<p>Compare DAX and M formulas in Visual Studio Code is really easy with the help&#8230;\t\t\t\t<\/div>\n\t\t\t\t\t\t<div class=\"uagb-post__text uagb-post__cta wp-block-button\">\n\t\t\t\t<a class=\"wp-block-button__link uagb-text-link\" href=\"https:\/\/thebipower.fr\/index.php\/2020\/09\/02\/compare-dax-and-m-formulas-in-visual-studio-code\/\" target=\"_self\" rel=\"bookmark noopener noreferrer\">Read More<\/a>\n\t\t\t<\/div>\n\t\t\t\t\t\t\t\t\t\t\t\t<\/article>\n\t\t\t\t\t\t\t\t\t\t\t<\/div>\n\t\t\t","protected":false},"excerpt":{"rendered":"<p>Get username function in Power Query with few lines of M code. For the moment, there is no native way to get the Windows username in M language. The username might be useful to filter a table or to get a file path in the &#8216;Documents&#8217; folder. The following post describes 4 ways to get&#8230;<\/p>\n","protected":false},"author":2,"featured_media":2914,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_uag_custom_page_level_css":"","_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_uf_show_specific_survey":0,"_uf_disable_surveys":false,"_kad_post_transparent":"","_kad_post_title":"","_kad_post_layout":"","_kad_post_sidebar_id":"","_kad_post_content_style":"","_kad_post_vertical_padding":"","_kad_post_feature":"","_kad_post_feature_position":"","_kad_post_header":false,"_kad_post_footer":false,"footnotes":""},"categories":[3],"tags":[22,24,26,68],"uagb_featured_image_src":{"full":["https:\/\/thebipower.fr\/wp-content\/uploads\/2020\/03\/Fx-get-username.png",343,97,false],"thumbnail":["https:\/\/thebipower.fr\/wp-content\/uploads\/2020\/03\/Fx-get-username-150x97.png",150,97,true],"medium":["https:\/\/thebipower.fr\/wp-content\/uploads\/2020\/03\/Fx-get-username-300x85.png",300,85,true],"medium_large":["https:\/\/thebipower.fr\/wp-content\/uploads\/2020\/03\/Fx-get-username.png",343,97,false],"large":["https:\/\/thebipower.fr\/wp-content\/uploads\/2020\/03\/Fx-get-username.png",343,97,false],"1536x1536":["https:\/\/thebipower.fr\/wp-content\/uploads\/2020\/03\/Fx-get-username.png",343,97,false],"2048x2048":["https:\/\/thebipower.fr\/wp-content\/uploads\/2020\/03\/Fx-get-username.png",343,97,false],"mailpoet_newsletter_max":["https:\/\/thebipower.fr\/wp-content\/uploads\/2020\/03\/Fx-get-username.png",343,97,false]},"uagb_author_info":{"display_name":"Didier TERRIEN","author_link":"https:\/\/thebipower.fr\/index.php\/author\/masterpower\/"},"uagb_comment_info":9,"uagb_excerpt":"Get username function in Power Query with few lines of M code. For the moment, there is no native way to get the Windows username in M language. The username might be useful to filter a table or to get a file path in the &#8216;Documents&#8217; folder. The following post describes 4 ways to get...","_links":{"self":[{"href":"https:\/\/thebipower.fr\/index.php\/wp-json\/wp\/v2\/posts\/2894"}],"collection":[{"href":"https:\/\/thebipower.fr\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/thebipower.fr\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/thebipower.fr\/index.php\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/thebipower.fr\/index.php\/wp-json\/wp\/v2\/comments?post=2894"}],"version-history":[{"count":11,"href":"https:\/\/thebipower.fr\/index.php\/wp-json\/wp\/v2\/posts\/2894\/revisions"}],"predecessor-version":[{"id":3818,"href":"https:\/\/thebipower.fr\/index.php\/wp-json\/wp\/v2\/posts\/2894\/revisions\/3818"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/thebipower.fr\/index.php\/wp-json\/wp\/v2\/media\/2914"}],"wp:attachment":[{"href":"https:\/\/thebipower.fr\/index.php\/wp-json\/wp\/v2\/media?parent=2894"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/thebipower.fr\/index.php\/wp-json\/wp\/v2\/categories?post=2894"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/thebipower.fr\/index.php\/wp-json\/wp\/v2\/tags?post=2894"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}