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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
SamQC
Helper I
Helper I

Problem connecting dataflow to a local folder

Hello everyone!

I would like to use dataflows to connect to some data that is stored in a folder (that folder contains many identical csv files that are generated daily).

 

Unfortunately, unlike on Power BI Desktop, Power Query in dataflows does not generate the table automatically and only the "Source" step is generated so I end up with a table that is not usable :

SamQC_2-1659069225385.png

 

As suggested elsewhere in this forum, I copied the code in the Advanced editor 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-1659068755542.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-1659068755538.png

 

If anyone knows how to solve this issue, your help would be immensly appreciated 🙂

1 ACCEPTED SOLUTION
bcdobbs
Super User
Super User

I think two options:

 

From Power BI desktop rather than copying the advanced editor code select all the queries (including the merge files custom function folder and right click copy. Then in the service use CTRL V on the query pane to paste them all in. I think you were just missing the reference to the custom function.

 

That said have you tried just clicking the double down arrow at the top of the content column when you do the from folder step directly in the service?



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

View solution in original post

3 REPLIES 3
SamQC
Helper I
Helper I

OMG!

 

I was so simple! I indeed just clicked on the double down arrows and it worked! I did not know you could just do this 😅

Thanks a lot @bcdobbs  !

bcdobbs
Super User
Super User

I think two options:

 

From Power BI desktop rather than copying the advanced editor code select all the queries (including the merge files custom function folder and right click copy. Then in the service use CTRL V on the query pane to paste them all in. I think you were just missing the reference to the custom function.

 

That said have you tried just clicking the double down arrow at the top of the content column when you do the from folder step directly in the service?



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

Hi! I'm trying to do a similar thing by pasting the code in the query editor, but I am prompted with the below message. Could you please advise how to resolve this issue?
Many thanks!

CCART92_0-1705480654445.png

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors