Transcribe worksheets to table

[frenchy988]

Im turning multiple same outlined worksheets that host information for different clients from a folder this into a table on this specific sheet i want to create multiple column based on column 2, like create columns: "Camp","Community Classes"... and row 1 would be whats on column 6. the trick there is because were analysing multiple budgets i was thinking after creating the column filling it down and removing duplicate to keep the correct info per budget.

[frenchy988]

wow thanks a lot, it seemed to work but would love to see you how you did that in action (loom video would be fire!!) thanks again

I stupidly copied and pasted the steps and ended up getting  a code that looks like this:

let
Source = Folder.Files("C:\Users\padout\OneDrive - Makor Care Network\Waiver - Waiver Team\FI\zExcel\New folder"),
#"Added Custom" = Table.AddColumn(Source, "Custom", each Excel.Workbook([Content])),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{ "Name","Custom"}),
#"Removed Errors" = Table.RemoveRowsWithErrors(#"Removed Other Columns", {"Custom"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Errors", "Custom", {"Data", "Item"}, {"Data", "Item"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded Custom", each [Item] = "IDGS "),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Item"}),
#"select_columns" = Table.TransformColumns(#"Removed Columns", {{"Data", each Table.SelectColumns(_, {"Column2", "Column9"})}}),
#"select_rows" = Table.TransformColumns(#"select_columns", {{"Data", each Table.SelectRows(Table.Skip(_, 8), each [Column2] <> null and [Column9] <> null)}}),
#"pivot_table" = Table.TransformColumns(#"select_rows", {{"Data", each Table.Pivot(_, List.Distinct(_[Column2]), "Column2", "Column9")}}),
#"Renamed Columns" = Table.RenameColumns(pivot_table,{{"Data", "IDGS-"}}),
#"Expanded IDGS-" = Table.ExpandTableColumn(#"Renamed Columns", "IDGS-", {"Community Classes & Publicly Available Training/Coaching", "Coaching/education for parent(s), spouse and advocates involved in the person's self-directed services", "Clinician Consultants, Independent Contractors - (Non-Direct Service Provision --Clinical Consultation Specialties)", "Clinician (Direct-Provision of Therapies/Therapeutic Activities Not Otherwise Funded in the state plan)", "Health Club/Organizational Memberships/Community Participation", "Household-Related Items and Services", "Paid Neighbor", "Self-Directed Staffing Support", "Transition Programs for Individuals with IDD", "Transportation", "Interpretation Services"}, {"IDGS-.Community Classes & Publicly Available Training/Coaching", "IDGS-.Coaching/education for parent(s), spouse and advocates involved in the per", "IDGS-.Clinician Consultants, Independent Contractors - (Non-Direct Service Provi", "IDGS-.Clinician (Direct-Provision of Therapies/Therapeutic Activities Not Otherw", "IDGS-.Health Club/Organizational Memberships/Community Participation", "IDGS-.Household-Related Items and Services", "IDGS-.Paid Neighbor", "IDGS-.Self-Directed Staffing Support", "IDGS-.Transition Programs for Individuals with IDD", "IDGS-.Transportation", "IDGS-.Interpretation Services"})
in
#"Expanded IDGS-"

[jgeddes · Accepted solution]

As long as the worksheets are the same in the sense that the categories and values are in the same columns then you can do what you are looking for.
As a basic example if I start with data that is similar to this...

I can do the transformations on the nested tables prior to expanding them.
The end result could look something like this...

You can paste the following code into the advanced editor of a blank query to see the steps used.

let
    Source = 
    #table(
        {"Name", "Table"},
        {
            {"FirstFile", #table(null, {{null, null, null},{"indirect items", null, null}, {"Category", "Annual Amount", "Monthly Amount"},{"category1", 21000, 2100}, {"category2", 15000, 1500}, {null, null, null}})},
            {"SecondFile", #table(null, {{null, null, null},{"indirect items", null, null}, {"Category", "Annual Amount", "Monthly Amount"},{"category1", 36000, 1700}, {"category2", 18000, 1200}, {null, null, null}})},
            {"ThirdFile", #table(null, {{null, null, null},{"indirect items", null, null}, {"Category", "Annual Amount", "Monthly Amount"},{"category1", 31000, 4100}, {"category2", 16000, 1600}, {"category3", 2300, 230}, {null, null, null}})}
        }
    ),
    select_columns = Table.TransformColumns(Source, {{"Table", each Table.SelectColumns(_, {"Column1", "Column2"})}}),
    select_rows = Table.TransformColumns(select_columns, {{"Table", each Table.SelectRows(Table.Skip(_, 3), each [Column1] <> null and [Column2] <> null)}}),
    pivot_table = Table.TransformColumns(select_rows, {{"Table", each Table.Pivot(_, List.Distinct(_[Column1]), "Column1", "Column2")}}),
    get_column_names = Table.AddColumn(pivot_table, "Column Names", each Table.ColumnNames([Table])),
    distinct_column_names = List.Distinct(Table.ExpandListColumn(get_column_names, "Column Names")[Column Names]),
    expand_nested_tables = Table.ExpandTableColumn(pivot_table, "Table", distinct_column_names)
in
    expand_nested_tables