I'm trying to combine the following three CSV files in power query.
These three files have the same columns. The CSV files do not contain the SN# and POLE information shown below - I got these from their file names.
When I click on each individual table to see its attributes, all three show their respective data, but after I expand the table attributes, only the information from the first file shows up. The other two files all have null values.
Here is the mcode:
let
Source = #"INCON DATA",
#"Filtered Rows" = Table.SelectRows(Source, each ([AREA] = "sf6-daily-summary.csv")),
#"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows", each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File from INCON DATA SF6-DAILY", each #"Transform File from INCON DATA SF6-DAILY"([Content])),
#"Removed Other Columns1" = Table.SelectColumns(#"Invoke Custom Function1",{"SN#", "Pole", "AREA", "Date created", "Transform File from INCON DATA SF6-DAILY"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from INCON DATA SF6-DAILY", Table.ColumnNames(#"Transform File from INCON DATA SF6-DAILY"(#"Sample File"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"DATE_STAMP", type date}, {"AVG_DENSITY_A", type number}, {"AVG_PRESSURE_A", type number}, {"AVG_TEMP_A", type number}, {"DENS_TREND_A", type number}, {"PRESS_TREND_A", type number}, {"TREND_CONF_A", Int64.Type}, {"AVG_MASS_A", type number}, {"MASS_CHANGE_A", Int64.Type}, {"ACCUM_MASS_LOSS_A", Int64.Type}})
in
#"Changed Type"
Can anybody help me out?
Thanks,
R
Hi and welcome to our forum!
It looks like the very first thing you do (second line of code) is filter the source to only return the file called sf6-daily-summary.csv which will be why you're only getting the data from one file. i.e. you've only chosen one file. If you remove this step do you get all of the data?
Mynda
Hi Mynda:
First, thank you for looking into this.
Unfortunately, removing that step did not get all of the data.
Also, this step is filtering several other "types" of CSV files that have dissimilar column headers. Please see the image below for clarification.
Moreover, the fifth step is where I'm running into trouble, I think. Up to this step, data for all three tables shows when I click on the separate tables (see below for example). It is when I try to expand the tables that data from the first file's table is the only data that shows up.
After I click on expand table...[added date filter for simplification] only data from the first file shows up.
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from INCON DATA SF6-DAILY", Table.ColumnNames(#"Transform File from INCON DATA SF6-DAILY"(#"Sample File"))),
Again, I appreciate the help with this.
Thanks,
R
Hi, the images don't help a lot because in one of them I don't know what step they're showing. What is the source of the data? It appears to be another query. I don't see how you can have multiple files in a folder with the same file name, which is what appears to be the case in one of the screenshots.
Perhaps you can share your files, the query file and the 3 CSV files you want to import. This will be far more helpful than screenshots.
Mynda
Mynda,
I've attached the three CSV files and the excel file which has the query.
This query only has the three files loaded, but it doesn't seem to get the data from the second and third file. It shows an error this time.
Thanks,
R
Hi,
Thanks for sharing the files. The 3 CSV files have different column headers. This is the problem. P1 column headers end in _A, P2 end in _B and P3 end in _C
They must all have identical column names. I assumed you had already checked this because you said initially that "these three files have the same columns."
Mynda
