Forum Discussion

frenchy988's avatar
frenchy988
Frequent Visitor
1 year ago
Solved

Transcribe worksheets to table

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 ...
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • jgeddes's avatar
    jgeddes
    1 year ago

    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
frenchy988's avatar
frenchy988
Frequent Visitor
1 year ago

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