Dataflow 101 - How to create dataflow based on a folder that combines multiple excel files?

[adelhumeau]

Good morning,

Please allow me to share a little context to explain a situation in which I will appreciate guidance...

• I started using PowerBI to generate monthly reports regarding company’s operations (I remain amazed with how powerful PowerBI is for these purposes)
• In the Power BI Desktop environment, to get data, I use the “folder” option as a data source. In my experience, this has been a phenomenal tool because it essentially allows the simultaneous upload and edit in the Power Query of multiple Excel files (assuming all files share similar characteristics, primarily commonality in the order of columns)
• Everything seemed to be working flawlessly, but the issue came when having to update reports with new monthly data. Basically I am having to recreate all the Power Query instructions each month for each of my reports to have data current. I presumed this was not the optimal way to go and think dataflows could positively be a game changer when it comes to update data.
• The following I think is the million-dollar question:
• Dataflows can only be created on Power BI Server environment (as opposed to the Desktop environment). Nonetheless the Server environment does not support a given “folder” as a data source. How can then I be able to create a dataflow which combines excel files hosted on a folder?

I believe the following three options to be potential solutions (but haven't been able to find a helpful resource as to how to accomplish task)

1. Get data from Database (e.g. Amazon Redshift, Sybase database, Oracle database, etc.)
2. Get data from Azure (e.g. Azure SQL database, Azure Tables, Azure Blobs, etc.)
3. Get data from Online Services (Is PowerApps considered an onlice service that can be used as a data source?)

TL; DR: I would appreciate any advice regarding what could be the best way to create a dataflow (on the Power BI Server environment) based on a folder that combines multiple excel files.

Thank you.

[v-diye-msft]

Hi @adelhumeau ,

Please use the desktop to get data in folder and then combine them, after that, copy the M code to blank query in the dataflow.

[SamQC]

Hello @v-diye-msft !

I am encoutering the same problem and I tried to apply your solution.

I copied the code from the Desktop:
let
Source = Folder.Files("MyPath"),
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File (2)", each #"Transform File (2)"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File (2)"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File (2)", Table.ColumnNames(#"Transform File (2)"(#"Sample File (2)"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"RESORT_CODE", type text}, {"CONFIRMATION_NO", Int64.Type}, {"TEL", type any}})
in
#"Changed Type"

However, I get this message :

And indeed, if I go to the step #"Invoke Custom Function1", I can see that the query gives an [Error] instead of a [Table] :

Do you know how I could overcome this issue ?

[SamQC]

I got the solution here : https://community.powerbi.com/t5/Service/Problem-connecting-dataflow-to-a-local-folder/m-p/2668717#M...

[u02cm62]

I would read this.  This might help you decide.

https://docs.microsoft.com/en-us/power-bi/service-dataflows-on-premises-gateways

[v-diye-msft]

Hi @u02cm62 ,

If  my above post helps, could you please consider Accept it as the solution to help the other members find it more quickly. thanks!