Forum Discussion
powerquery from folder
When you use "Folder" as a source in Power Query, it usually spins up a helper function, sample file parameter, and sample file query all inside a folder in your list of Queries. I don't see that here.
Also, the Folder source only works out of the box if you have flat files (CSV or txt). If you want to use it on Excel (and I see a bit of "...xlsx" in one of the columns, you need to do additional work. So how did you combine multiple Excel files to get ONE table? Was it a UNION ?
I went away from using the helper function by adding a column = Table.AddColumn(Source, "Custom", each Excel.Workbook([Content])) then expend pick the right sheet. and from there I add column and fill down. then filter unique value.
I ended up working it out with 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 "),
#"Expanded Data" = Table.ExpandTableColumn(#"Filtered Rows", "Data", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23", "Column24", "Column25", "Column26", "Column27", "Column28"}, {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23", "Column24", "Column25", "Column26", "Column27", "Column28"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Expanded Data",{"Name", "Column2", "Column6", "Column9"}),
#"Removed Duplicates" = Table.Distinct(#"Removed Other Columns1"),
#"Added Conditional Column" = Table.AddColumn(#"Removed Duplicates", "IDGS-Camp", each if [Column2] = "Camp" then [Column9] else null),
#"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "IDGS-Community Classes & Publicly Available Training/Coaching", each if [Column2] = "Community Classes & Publicly Available Training/Coaching" then [Column9] else null),
#"Added Conditional Column2" = Table.AddColumn(#"Added Conditional Column1", "IDGS-Clinician Consultants, Independent Contractors - (Non-Direct Service Provision --Clinical Consultation Specialties)", each if [Column2] = "Clinician Consultants, Independent Contractors - (Non-Direct Service Provision --Clinical Consultation Specialties)" then [Column9] else null),
#"Added Conditional Column3" = Table.AddColumn(#"Added Conditional Column2", "IDGS-Clinician (Direct-Provision of Therapies/Therapeutic Activities Not Otherwise Funded in the state plan)", each if [Column2] = "Clinician (Direct-Provision of Therapies/Therapeutic Activities Not Otherwise Funded in the state plan)" then [Column9] else null),
#"Added Conditional Column4" = Table.AddColumn(#"Added Conditional Column3", "IDGS-Health Club/Organizational Memberships/Community Participation", each if [Column2] = "Health Club/Organizational Memberships/Community Participation" then [Column9] else null),
#"Added Conditional Column5" = Table.AddColumn(#"Added Conditional Column4", "IDGS-Household-Related Items and Services", each if [Column2] = "Household-Related Items and Services" then [Column9] else null),
#"Added Conditional Column6" = Table.AddColumn(#"Added Conditional Column5", "IDGS-Paid Neighbor", each if [Column2] = "Paid Neighbor" then [Column9] else null),
#"Added Conditional Column7" = Table.AddColumn(#"Added Conditional Column6", "IDGS-Self-Directed Staffing Support", each if [Column2] = "Self-Directed Staffing Support" then [Column9] else null),
#"Added Conditional Column8" = Table.AddColumn(#"Added Conditional Column7", "IDGS-Transition Programs for Individuals with IDD", each if [Column2] = "Transition Programs for Individuals with IDD" then [Column9] else null),
#"Added Conditional Column9" = Table.AddColumn(#"Added Conditional Column8", "IDGS-Transportation", each if [Column2] = "Transportation" then [Column9] else null),
#"Added Conditional Column10" = Table.AddColumn(#"Added Conditional Column9", "IDGS-Interpretation Services", each if [Column2] = "Interpretation Services" then [Column9] else null),
#"Filled Down" = Table.FillDown(#"Added Conditional Column10",{"IDGS-Camp", "IDGS-Community Classes & Publicly Available Training/Coaching", "IDGS-Clinician Consultants, Independent Contractors - (Non-Direct Service Provision --Clinical Consultation Specialties)", "IDGS-Clinician (Direct-Provision of Therapies/Therapeutic Activities Not Otherwise Funded in the state plan)", "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"}),
#"Removed Columns" = Table.RemoveColumns(#"Filled Down",{"Column2", "Column6", "Column9"}),
#"Filtered Rows1" = Table.SelectRows(#"Removed Columns", each ([#"IDGS-Interpretation Services"] <> null)),
#"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows1",{{"IDGS-Camp", Int64.Type}, {"IDGS-Community Classes & Publicly Available Training/Coaching", Int64.Type}, {"IDGS-Clinician Consultants, Independent Contractors - (Non-Direct Service Provision --Clinical Consultation Specialties)", Int64.Type}, {"IDGS-Clinician (Direct-Provision of Therapies/Therapeutic Activities Not Otherwise Funded in the state plan)", Int64.Type}, {"IDGS-Health Club/Organizational Memberships/Community Participation", Int64.Type}, {"IDGS-Household-Related Items and Services", Int64.Type}, {"IDGS-Paid Neighbor", Int64.Type}, {"IDGS-Self-Directed Staffing Support", Int64.Type}, {"IDGS-Transition Programs for Individuals with IDD", Int64.Type}, {"IDGS-Transportation", Int64.Type}, {"IDGS-Interpretation Services", Int64.Type}}),
#"Multiplied Column" = Table.TransformColumns(#"Changed Type", {{"IDGS-Camp", each _ * 10, type number}}),
#"Multiplied Column1" = Table.TransformColumns(#"Multiplied Column", {{"IDGS-Community Classes & Publicly Available Training/Coaching", each _ * 10, type number}}),
#"Multiplied Column2" = Table.TransformColumns(#"Multiplied Column1", {{"IDGS-Clinician Consultants, Independent Contractors - (Non-Direct Service Provision --Clinical Consultation Specialties)", each _ * 10, type number}}),
#"Multiplied Column3" = Table.TransformColumns(#"Multiplied Column2", {{"IDGS-Clinician (Direct-Provision of Therapies/Therapeutic Activities Not Otherwise Funded in the state plan)", each _ * 10, type number}}),
#"Multiplied Column4" = Table.TransformColumns(#"Multiplied Column3", {{"IDGS-Health Club/Organizational Memberships/Community Participation", each _ * 10, type number}}),
#"Multiplied Column5" = Table.TransformColumns(#"Multiplied Column4", {{"IDGS-Household-Related Items and Services", each _ * 10, type number}}),
#"Multiplied Column6" = Table.TransformColumns(#"Multiplied Column5", {{"IDGS-Paid Neighbor", each _ * 10, type number}}),
#"Multiplied Column7" = Table.TransformColumns(#"Multiplied Column6", {{"IDGS-Self-Directed Staffing Support", each _ * 10, type number}}),
#"Multiplied Column8" = Table.TransformColumns(#"Multiplied Column7", {{"IDGS-Transition Programs for Individuals with IDD", each _ * 10, type number}}),
#"Multiplied Column9" = Table.TransformColumns(#"Multiplied Column8", {{"IDGS-Transportation", each _ * 10, type number}}),
#"Multiplied Column10" = Table.TransformColumns(#"Multiplied Column9", {{"IDGS-Interpretation Services", each _ * 10, type number}})
in
#"Multiplied Column10"