- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Files from a folder - adding columns
Hi,
I have three csv files in a folder that Power BI is magically appending as a data source.
I want to add a couple of columns to the files, but everything I've tried to add them seems to break something else. I have a few additional steps applied in PQE already after the files are appended, but I don't know if this is causing any of the issues.
Can anyone recommend a simple step by step guide to get a couple of extra columns from the source csv files?
Thanks 🙂
I'm not sure if screenshots are useful, but...
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Here is an example that combines all .csv files in C:\temp. It assumes that the files have the same structure, and provides dummy column headers - you may want to provide your own. It also doesn't strip out potentially existing column headers inside the files - you can filter those out if needed, or modify the code for the AddColumn accordingly.
let
Source = Folder.Files("C:\Temp"),
#"Filtered Rows" = Table.SelectRows(Source, each ([Extension] = ".csv")),
#"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each Csv.Document([Content])),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6"}, {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6"})
in
#"Expanded Custom"
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Apologies for not coming back to this discussion sooner - the perils of having too many different projects on the go at once!
Thanks for all the help 🙂
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @JohnHedges,
Did lbendlin 's suggestions help with your scenario? if that is the case, you can consider Kudo or accept his suggestions to help others who faced similar requirements.
If these also don't help, please share more detailed information to help us clarify your scenario to test.
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
If this post helps, please consider accept as solution to help other members find it more quickly.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Don't worry, this is a common pattern. Power Query is trying to be helpful and when you click on the "Combine binaries" icon it will add all kinds of helper functions and backlinks and what have you.
You don't need any of that. Instead of clicking the "combine" button you can add a custom column that contains the same transformation for each row's binary field, like for example Csv.Document([Content]). Then you can expand that column and keep your existing columns or add new ones etc. One thing you need to have ready is a list of columns that you want to expand into (that's what the helper function would normally do)
Let me know if you manage to do it based on this description or if you like a code sample.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks for taking the time to reply 🙂
An example would be fantastic. It's a rather large project using a lot of different folders as sources and I'm terrified of breaking it (also I'm trying to avoid having to kill part of model and rebuild from scratch, just to get two extra columns!)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Here is an example that combines all .csv files in C:\temp. It assumes that the files have the same structure, and provides dummy column headers - you may want to provide your own. It also doesn't strip out potentially existing column headers inside the files - you can filter those out if needed, or modify the code for the AddColumn accordingly.
let
Source = Folder.Files("C:\Temp"),
#"Filtered Rows" = Table.SelectRows(Source, each ([Extension] = ".csv")),
#"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each Csv.Document([Content])),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6"}, {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6"})
in
#"Expanded Custom"
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks for taking the time to create an example.
At the risk of looking like an idiot, I just don't know where to put it. I'll have a fiddle around with my back up file and see if I can work it out!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Go to Power Query by selecting Get Data...Blank Query. Then click Advanced Editor and replace the code with the one I posted.
Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Power BI Monthly Update - January 2025
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
Subject | Author | Posted | |
---|---|---|---|
07-12-2024 10:19 AM | |||
09-20-2024 04:46 AM | |||
06-28-2024 11:53 AM | |||
08-02-2024 07:03 AM | |||
02-07-2023 12:31 AM |
User | Count |
---|---|
104 | |
75 | |
43 | |
39 | |
32 |
User | Count |
---|---|
168 | |
90 | |
65 | |
46 | |
44 |