Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Getting data from CSV files that have slightly different columns each week

I have a CSV file, when stripped down to the barest form, that looks like this:   WW33   Job,WW31,WW32,WW33 JobA,4,4,4 JobB,2,6,3 JobC,2,4,7     As you can probably guess, this is weekly dat...
General Comment
  • AlB's avatar
    AlB
    6 years ago

    Hi Anonymous 

    Try this. It's basically loading the files from the folder, transposing the tables, eliminating duplicates and transposing again

    let
    Source = Folder.Files(FOLDER_WHERE_YOUR_DATA_IS),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Csv.Document([Content])),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Table.Transpose([Custom])),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom1",{"Custom.1"}),
    #"Expanded Custom.1" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom.1", {"Column1", "Column2", "Column3", "Column4"}, {"Column1", "Column2", "Column3", "Column4"}),
    #"Removed Duplicates" = Table.Distinct(#"Expanded Custom.1", {"Column1"}),
    #"Transposed Table" = Table.Transpose(#"Removed Duplicates"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Job", type text}, {"WW31", Int64.Type}, {"WW32", Int64.Type}, {"WW33", Int64.Type}, {"WW34", Int64.Type}, {"WW35", Int64.Type}})
in
    #"Changed Type"

     

    Please mark the question solved when done and consider giving kudos if posts are helpful.

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.

    Cheers 

     

Anonymous's avatar
Anonymous
Not applicable
6 years ago

OK, I do believe that I figured it out.

 

This will go through all the columns without having to specify their names.

 

#"Expanded Custom.1" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom.1", List.Union(List.Transform(#"Removed Other Columns"[Custom.1], each Table.ColumnNames(_)))),
AlB's avatar
AlB
Community Champion
6 years ago

Anonymous 

Yep. Elegant solution  🙂

 

 

 

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    try if this (or a modified version)  function could be useful to your job.

     

     

     

     

    let
    conc=(utab,ntab)=>
    let
    lstabs=List.Transform({1..ntab}, each "w"&Text.From(Number.From(Text.End(utab,2))-ntab+_-1)),
    Cws=Table.TransformRows(Expression.Evaluate(utab,#shared), (ru)=>Record.Combine(List.Transform(lstabs, each Expression.Evaluate(_,#shared){[Job=ru[Job]]})&{ru}))
    in
    Table.FromRecords(Cws)
in
   conc

     

     

     

     

    the function takes in input a tablename (as text) and a number (the number of table you want to concat with the leader table. The names are supposed to be leadername+decreasing number as suffix. But if you have a list of table from other source there is no need to costruct the list names)

    The result is a table which is a concatenation of all tables.