In Part 1 of this little series I described the core-Flow on how to automatically validate E-mail attachments with Flow and Power BI. It automatically sends an e-mail to a business partner who sent an attachment, that didn’t meet the agreed specifications:
Automatically validate e-mail attachments – Part1
But before going live with this Flow, you should consider the following aspects:
Receiving files from business partners that don’t match the agreed requirements/formats causes all sorts of problems in daily business. I found it particularly disturbing during month-end closing when time is really tight: You have a strict rule in which order each process has to run and there are many dependencies between them. So when then one import doesn’t work, many other processes will come to a halt as well. Fortunately, today there is a simple remedy for it: Automatically validate E-mail attachments with Flow and Power BI
You can create a Flow that “listens” for incoming emails in a mailbox that match certain criteria and contain attachments. Flow can then extract these attachments and save them to an online-folder. After that, Flow triggers a refresh of a Power BI dataset, that imports these attachments and checks for the data quality-criteria that you have defined. Then you create measures for the data quality that trigger data driven alerts from Power BI service. Flow then listens for these alerts and sends an email back to the sender, requesting for a corrected file.
This not just saves crucial time, but also your nerves (and those of your team-mates).
Some time ago I published a function that extracts all M-code from Power BI (.pbix)-files. Today I publish the pendant to Bulk-extract Power Query M-code from multiple Excel-files at once. The code contains many elements from the before mentioned, so please refer to that article for reference.
The function below has just one parameter where you either fill in a full filename (incl. path) of an Excel file, or a folder path where multiple files reside. The function will automatically detect the right modus and spit out the M-code. Read more
The demand to unstacking a column into a table is not rare (see here for example: PowerBIForum ) . Also if you copy a table from a post in the Power BI community forum to the enter-data-section in Power BI, it will show up as such a one-column-table.
Unstack column to table
Table.FromRows( List.Split( Table1[Column1], 3) )
..provided that your table is named “Table1”
Sometimes I need to retrieve the textual representation of a type in Power Query and I’m using a fairly verbose function that I’ve stolen ages ago (I believe it was here: https://ssbi-blog.de/technical-topics-english/power-query-editor-using-notepad/ ) :
The following steps show how to create a new column in a table using existing custom function code. This works in Power BI as well as in Power Query in Excel:
