Folder source in Power BI Dataflows
Dataflows in Power BI Service could be a real revolution for people who lack of power when loading their datasources in Power BI Desktop.
Dataflows can be explained as “Power Query as a Service” because, in a new (app) workspace, you are now allowed to create a query just like in the query editor of the Power BI Desktop. A Pro licence is enough to use dataflows.
Even if you already don’t see all the buttons of the editor ribbon, the M language is compatible and a efficient trick is to create the M query on your desktop, copy and paste into a blank query in a dataflow’s entity.
Some less usuals sources are missing in the web interface. But the M code should manage to query them ! The Microsoft documentation gives us the list of these datasources :
• SAP Business Warehouse
• Azure Analysis Services
• Adobe Analytics
• ODBC
• OLE DB
• Dossier
• Dossier SharePoint Online
• Dossier SharePoint
• HDFS Hadoop
• Azure HDInsight (HDFS)
• Fichier Hadoop (HDFS)
• Informix (bêta)
Wait, folder is not so unsual ! I know a lot of users who really enjoy the simplicity of the “Combine & load” on a folder containing many files with same structure. It so simple beacause Power Query creates on the fly a M function which is applied to all the rows of the source table and precisely to the “content” column. This column contents a binary vision of each file.
Trying to use the same way in a dataflow, we can’t refer inside a entity (query name in dataflow) to a external one (maybe in Premium).
But is the function really necessary ? The each operator is the solution ! (Thanks to Robin L & Erwan B, my colleagues) The each operator is the way to iterate a calculation over the rows of a table. We combine the two operations we have to do on the file :
- Csv.Document() with the good parameters (Delimiter, number of Columns, Encoding, QuoteStyle)
- PromoteHeaders()
At the next step, your files are ready to be combined ! Just click on the Expand button and don’t forget to type the columns.
Here is a sample of code for a 3 fields structure.
let
folder = “C:\your_folder_path”,
colNumber = 3,
Source = Folder.Files(folder),
KeepBinary = Table.SelectColumns(Source,{“Content”}),
ReadTable = Table.AddColumn( KeepBinary, “Table”, each Table.PromoteHeaders(Csv.Document( [Content], [Delimiter=”,”, Columns=colNumber, Encoding=1252, QuoteStyle=QuoteStyle.None]), [PromoteAllScalars=true]) ),
ExpandTable = Table.ExpandTableColumn(ReadTable, “Table”, {“col1”, “col2”, “col3”}, {“col1”, “col2”, “col3”}),
DeleteBinary = Table.RemoveColumns(ExpandTable,{“Content”}),
in
DeleteBinary
You will need a professional data gateway to identify the folder path.
Don’t forget to refresh one time the dataflow in order to see the data when you connect with Power BI Desktop.
Enjoy dataflows !