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.
Hey All,
Is it possible to connect to a folder and import all the files so that the Query panel will be clean with only the Table name?
As you can see in the picture, it looks really messy with all the extra folders!
Thanks.
Solved! Go to Solution.
You can include everything in your main query.
You can copy the "Transform Sample File..." code into the #"Invoke Custom Function1" step and replace the parameter by [Content].
Additionally, you need to adjust the #"Expand Table Column1" step, so the column names for the expanded table are derived from the first table in the column with the nested tables.
Now you don't need the other query objects any more. To be honest I had some difficulties removing them and I created a new pbix file, but maybe you can just remove them.
An example below; adjust as appropriate.
let Source = Folder.Files(".................."), #"Filtered Rows" = Table.SelectRows(Source, each ([Extension] = ".csv")), #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Rows", "Transform File from Binary", each let Source = Csv.Document([Content],[Delimiter=" ", Columns=2, Encoding=1252, QuoteStyle=QuoteStyle.None]), #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]) in #"Promoted Headers"), #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}), #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File from Binary"}), #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from Binary", Table.ColumnNames(#"Invoke Custom Function1"[#"Transform File from Binary"]{0})), #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Test1", Int64.Type}, {"Test2", Int64.Type}}) in #"Changed Type"
@Beyondforce Haha you like me i also hate that, just collapse the folder so you cant see it expanded, if you create a combined file it has to create a sample query and
Proud to be a Super User!
I was thinking... I'm trying to import CSV files, so maybe the Excel.Workbook function works only on Excel files!
Makes sense, how hard would it be for you to convert it?
Proud to be a Super User!
She just answered me, that this command it's only for excel files, BUMMER!
Those cvs file are been generated automaticaly from our Main Frame! So I just want to pick them up and make reports from the data.
You can include everything in your main query.
You can copy the "Transform Sample File..." code into the #"Invoke Custom Function1" step and replace the parameter by [Content].
Additionally, you need to adjust the #"Expand Table Column1" step, so the column names for the expanded table are derived from the first table in the column with the nested tables.
Now you don't need the other query objects any more. To be honest I had some difficulties removing them and I created a new pbix file, but maybe you can just remove them.
An example below; adjust as appropriate.
let Source = Folder.Files(".................."), #"Filtered Rows" = Table.SelectRows(Source, each ([Extension] = ".csv")), #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Rows", "Transform File from Binary", each let Source = Csv.Document([Content],[Delimiter=" ", Columns=2, Encoding=1252, QuoteStyle=QuoteStyle.None]), #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]) in #"Promoted Headers"), #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}), #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File from Binary"}), #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from Binary", Table.ColumnNames(#"Invoke Custom Function1"[#"Transform File from Binary"]{0})), #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Test1", Int64.Type}, {"Test2", Int64.Type}}) in #"Changed Type"
Hi,
I know this is an old post but the solution is still Good !!
Just wondering - I need to use a Template file as my source files change format over time. How to you update this code to allow you to use a template file?
Cheers
Andrew
@vanessafvg, Have you seen this video? https://www.youtube.com/watch?v=l_CgIOwSjmU
It seems possible, but it didn't work for me for some reason! Let me know it works for you.
Can't believe I missed it, i always watch the curbal stuff. That's an awesome find, i will have to try it thanks! Why didnt it work for you?
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |