Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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 examp...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

     

     

     

     

     

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

Anonymous's avatar
Anonymous
Not applicable
5 years ago

 

 

 

 

 

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

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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"
      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Anonymous 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