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

 

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.  

10 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    "How could I write this logic in M?"

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      You're right I should have shared the sheet. I've updated the link in my post to link to the file.

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        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"