Forum Discussion
Use power query to split csv file not multiple tables
#"Raggruppate righe" = Table.Group(YOUR_LAST_STEP, {"Attendee Report", "Column3"}, {{"all", each Table.PromoteHeaders(Table.Skip(_))}},GroupKind.Local,(x,y)=>Number.From(x[Column3]<>null or y[Column3]=null)), #"Rimosse colonne" = Table.RemoveColumns(#"Raggruppate righe",{"Column3"}) in #"Rimosse colonne"it is not clear to me where (in what statement) you get the error you report. My code is just pretty much the following two lines. Load the table you want and queue these lines by adapting the name YOUR_LAST_STEP. In theory, one could also make a function of these lines, if it were useful.
Thank you Anonymous . I learned a lot studing your interpretation. This is essentially what I am trying to do but instead of with one file as an input, I want to import multiple files. I tried modifying your M code by using the code from PBI's standard "get data form folder" option but I can not figure out how I would integrate it into your code. I tired taking your last step "Changed Type" and using it in the "91718355517 - Attendee Report_e_Sheet" step.
I get an error:
Expression.Error: The key didn't match any rows in the table.
Details:
Key=
Item=91718355517 - Attendee Report_e
Kind=Sheet
Table=[Table]
I'm new to M and lack a lot of knowledge about the language. What is the purpose of the "91718355517 - Attendee Report_e_Sheet" step in your orginal interpretation? How can I use your code but on a folder? Any help is greatly appreciated.
let
Source = Folder.Files("C:\Users\Mike\Desktop\New folder\logs"),
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File (3)", each #"Transform File (3)"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File (3)"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File (3)", Table.ColumnNames(#"Transform File (3)"(#"Sample File (3)"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}}),
#"91718355517 - Attendee Report_e_Sheet" = #"Changed Type"{[Item="91718355517 - Attendee Report_e",Kind="Sheet"]}[Data],
#"Intestazioni alzate di livello" = Table.PromoteHeaders(#"91718355517 - Attendee Report_e_Sheet", [PromoteAllScalars=true]),
#"Modificato tipo" = Table.TransformColumnTypes(#"Intestazioni alzate di livello",{{"Attendee Report", type text}, {"Column2", type any}, {"Column3", type any}, {"Column4", type any}, {"Column5", type any}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}, {"Column9", type any}, {"Column10", type any}, {"Column11", type text}, {"Column12", type text}}),
#"Raggruppate righe" = Table.Group(#"Modificato tipo", {"Attendee Report", "Column3"}, {{"all", each Table.PromoteHeaders(Table.Skip(_))}},GroupKind.Local,(x,y)=>Number.From(x[Column3]<>null or y[Column3]=null)),
#"Rimosse colonne" = Table.RemoveColumns(#"Raggruppate righe",{"Column3"})
in
#"Rimosse colonne"
let
Source = Folder.Files("C:\Users\Mike\Desktop\New folder\logs"),
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File (3)", each #"Transform File (3)"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File (3)"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File (3)", Table.ColumnNames(#"Transform File (3)"(#"Sample File (3)"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}}),
#"91718355517 - Attendee Report_e_Sheet" = #"Changed Type"{[Item="91718355517 - Attendee Report_e",Kind="Sheet"]}[Data],
#"Intestazioni alzate di livello" = Table.PromoteHeaders(#"91718355517 - Attendee Report_e_Sheet", [PromoteAllScalars=true]),
#"Modificato tipo" = Table.TransformColumnTypes(#"Intestazioni alzate di livello",{{"Attendee Report", type text}, {"Column2", type any}, {"Column3", type any}, {"Column4", type any}, {"Column5", type any}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}, {"Column9", type any}, {"Column10", type any}, {"Column11", type text}, {"Column12", type text}}),
#"Raggruppate righe" = Table.Group(#"Modificato tipo", {"Attendee Report", "Column3"}, {{"all", each Table.PromoteHeaders(Table.Skip(_))}},GroupKind.Local,(x,y)=>Number.From(x[Column3]<>null or y[Column3]=null)),
#"Rimosse colonne" = Table.RemoveColumns(#"Raggruppate righe",{"Column3"})
in
#"Rimosse colonne"
#"Raggruppate righe" = Table.Group(YOUR_LAST_STEP, {"Attendee Report", "Column3"}, {{"all", each Table.PromoteHeaders(Table.Skip(_))}},GroupKind.Local,(x,y)=>Number.From(x[Column3]<>null or y[Column3]=null)),
#"Rimosse colonne" = Table.RemoveColumns(#"Raggruppate righe",{"Column3"})
in
#"Rimosse colonne"
it is not clear to me where (in what statement) you get the error you report. My code is just pretty much the following two lines. Load the table you want and queue these lines by adapting the name YOUR_LAST_STEP. In theory, one could also make a function of these lines, if it were useful.
Thank you. I am able to use your two lines of code successfully on a single xlsx file, but not a csv file. Also I can't get it to work on a folder of xlsx files. In both cases I get the following error. Any ideas? My end goal is use this on a folder of csv files
Expression.Error: The column 'Attendee Report' of the table wasn't found.
Details:
Attendee ReportWith the concise information you provide, I (but maybe no one here) can't imagine where and why you get this error. If you report an error you should show where (the first item that gives the error) this happens. But more generally, in order to get some help, you should try to rebuild a folder with demo files and upload along with the code you use so you can do some tests. You should also explain what you are starting from and what you want to achieve. For example, the files in the folder that you need to process, are they fixed or are they updated from time to time? if they are variables with which rules do the names and numbers change? etc. etc.
I did as you said and rebuild my query and uploaded a folder of csv files. These files are meeting logs. My goal is to make a dashboard for these meeting logs. There will be new files added to this folder periodically so I'd like to refresh this dashboard based on these new files.
Sample folder:
https://drive.google.com/drive/folders/19KcLeM44MVaNq5MP0bGQzUujwid7c3Km?usp=sharing)
Below is the code I used to import the folder of csv files.
On the #"Raggruppate righe" step I am gettting this error:
Expression.Error: The column 'Attendee Report' of the table wasn't found.
Details:
Attendee ReportIs this enough information to understand this error?
let Source = Folder.Files("C:\Users\Mike\Desktop\New folder\Sample Logs"), #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true), #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])), #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}), #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}), #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))), #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}}), #"Raggruppate righe" = Table.Group(#"Changed Type", {"Attendee Report", "Column3"}, {{"all", each Table.PromoteHeaders(Table.Skip(_))}},GroupKind.Local,(x,y)=>Number.From(x[Column3]<>null or y[Column3]=null)), #"Rimosse colonne" = Table.RemoveColumns(#"Raggruppate righe",{"Column3"}) in #"Rimosse colonne"