Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
adelhumeau
New Member

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

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.

5 REPLIES 5
v-diye-msft
Community Support
Community Support

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.0001.PNG

 

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.

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 :

SamQC_0-1658993939331.png


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

SamQC_1-1658994037539.png

 

Do you know how I could overcome this issue ?

u02cm62
Helper V
Helper V

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!

 

 

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors