cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
heroyo
Helper I
Helper I

Use power query to split csv file not multiple tables

Hi, 

I have used power query in the past but am not an expert. I have a fold of logs that I want to import into PBI to create a dashboard. The log files have multiple tables on one sheet. (see example file in link) I was thinking the best way to handle with was to query the folder and create a custom column that I could use to filter and select the appropriate table by querying the main query. I know the name of the table headings (highlighted in yellow) but how would I create the logic for this filtering column? How could I write this logic in M?

https://docs.google.com/spreadsheets/d/171UxIzo3DwZjEjOmCeW1HeQkpJGSKsKrRnPXn2JcCgk/edit?usp=sharing 

 

1 ACCEPTED SOLUTION

 

 

 

 

 

    #"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.  

View solution in original post

10 REPLIES 10
Rocco_sprmnt21
Super User II
Super User II

"How could I write this logic in M?"

The first step is to upload a file not an image ...

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

 

immagine.png

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 @Rocco_sprmnt21 . 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.  

View solution in original post

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

With 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 Report

 

Is 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"

@Rocco_sprmnt21 I've been playing around with it but still can't get it to work with csv files or folders. The error in my previous comment was coming from the fact that I needed to assign new column names when importing csv or folders. I renamed them to follow the logic in your grouping expressions. The error went away but the output is not correct. It gives me a table of empty tables. For some reason it looks like the grouping expression isn't working. Can you explain the logic of #"Raggruppate righe" and  #"Rimosse colonne" in more detail? 

 

In the link below I have my pbix file and samples of the logs in both csv and xlsx

 

https://drive.google.com/drive/folders/19KcLeM44MVaNq5MP0bGQzUujwid7c3Km?usp=sharing 

I was able to figure out why these expressions were not filtering properly for csv files but worked for xlsx files. Blank values in xlsx files are imported into PBI as "null" where blanks are treated differenctly in csv files. To solve this issue I had to replace all blanks in csv with "null" values. 

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors