Forum Discussion

dataPhix's avatar
dataPhix
Frequent Visitor
5 years ago
Solved

Power Query: Include all unique columns found in files from folder

I've recently run into an issue which hopefully is solvable. Currently, I have power query pointing at a folder containing several CSV files. This is normally no issue, however, in this instance n...
Need Help
  • Fowmy's avatar
    Fowmy
    5 years ago

    dataPhix 

    Can you paste this code and follow the steps to check if it works for you?
    Enter your folder path

    let
    Source = Folder.Files("C:\xx\xx"),
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Content", "Name"}),
    #"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Custom", each Table.PromoteHeaders(Csv.Document([Content]))),
    Custom1 = Table.Combine({#"Added Custom"},{"Name", "Custom"}),
    #"Sorted Rows" = Table.Sort(Custom1,{{"Name", Order.Descending}}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Sorted Rows", "Custom", {"one", "two", "yes", "no", "may be", "three", "four"}, {"one", "two", "yes", "no", "may be", "three", "four"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded Custom",{"Name", "one", "two", "three", "four", "yes", "no", "may be"}),
    #"Sorted Rows1" = Table.Sort(#"Reordered Columns",{{"Name", Order.Ascending}})
in
    #"Sorted Rows1"

    ________________________

    If my answer was helpful, please consider Accept it as the solution to help the other members find it

    Click on the Thumbs-Up icon if you like this reply 🙂

    YouTube  LinkedIn

     



dataPhix's avatar
dataPhix
Frequent Visitor
5 years ago

As was suggested, below are the tables in a CSV format for your conveinince:

 

TABLE1

one,two,three
1,4,7
2,5,8
3,6,9

TABLE2

one,two,three,four
1,6,11,16
2,7,12,17
3,8,13,18
4,9,14,19
5,10,15,20

TABLE3

one,two,yes,no,maybe
1,1,1,1,1
2,2,2,2,2
3,3,3,3,3
4,4,4,4,4
5,5,5,5,5

 

This is not the actual data I am handling as it is sensitive in nature and cannot be shared here.  I need to point Power Query at a folder which contains 11 csv files with anywhere from 32-56 columns a piece.  It should return one final table with one column for each distinct column header found in any of the 11 files.

 

Thanks!

  • Fowmy's avatar
    Fowmy
    Super User
    5 years ago

    dataPhix 

    Can you paste this code and follow the steps to check if it works for you?
    Enter your folder path

    let
    Source = Folder.Files("C:\xx\xx"),
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Content", "Name"}),
    #"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Custom", each Table.PromoteHeaders(Csv.Document([Content]))),
    Custom1 = Table.Combine({#"Added Custom"},{"Name", "Custom"}),
    #"Sorted Rows" = Table.Sort(Custom1,{{"Name", Order.Descending}}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Sorted Rows", "Custom", {"one", "two", "yes", "no", "may be", "three", "four"}, {"one", "two", "yes", "no", "may be", "three", "four"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded Custom",{"Name", "one", "two", "three", "four", "yes", "no", "may be"}),
    #"Sorted Rows1" = Table.Sort(#"Reordered Columns",{{"Name", Order.Ascending}})
in
    #"Sorted Rows1"

    ________________________

    If my answer was helpful, please consider Accept it as the solution to help the other members find it

    Click on the Thumbs-Up icon if you like this reply 🙂

    YouTube  LinkedIn