Part 2: Automatically validate E-mail attachments with Flow and Power BI

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:

Caveats

Read more

Automatically validate E-mail attachments with Flow and Power BI

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

Process Automation 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).

The Details

Read more

Bulk-extract Power Query M-code from multiple Excel files at once

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.

How to use

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

Easy unstacking of columns in Power BI and Power Query (and Excel)

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.

Task

Unstack column to table

 

Solution

Table.FromRows( List.Split( Table1[Column1], 3) )

..provided that your table is named “Table1”

How it works

Read more