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.
You're right I should have shared the sheet. I've updated the link in my post to link to the file.
the second step is ...explain in detail with example tables what you want to achieve.
In the meantime, I propose an interpretation of your thoughts
let
Origine = Excel.Workbook(File.Contents("C:\Users\sprmn\Downloads\91718355517 - Attendee Report_example.xlsx"), null, true),
#"91718355517 - Attendee Report_e_Sheet" = Origine{[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"
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 Report