powerquery from folder

[frenchy988]

Im trying to analyse budgets that have the same set up for multiple clients from one folder and turn it into a table, the way i went about it it to create a conditionnal column for each of the subcategory from column 2 and pull the info from column 6. My issue, in column 6 some value are blank so when i go about filling down the row to be able to analyse multiple budget I get value from other budget as i dont have a proper reference for it.

[ZhangKun]

The formula you provided is too long, and there is no sample file, so I can't give you too much information. Here are some suggestions:

1. Is the position of each column fixed? For example, is the "Category" column always merged with 4 columns? Do all columns follow this rule? Is the numerical index of the first column always accurate? This information is very important, but it seems to be fixed.

2. There is a trap about merging cells, which we called "disguised merged cells". For example, fill 1 in cell A1 and 2 in cell A2, and then use the "Format Painter" to turn A1:A2 into a merged cell. Although only 1 is displayed on the surface, there is still the number 2 in A2.

3. In fact, it is not a good idea to do this. You should skip the useless rows in the front and back (rows 1-6, rows after row 19, as shown in the screenshot). Of course, before that, you may need to keep some information, such as the total of Annual Cost (to check whether the final result is correct), LastName (first row), etc.

4. For the Table.TransformColumns function, you can use Table.TransformColumns(tbl, {{...}, {...},{...}}).

To answer your question: If Annual Cost is always two columns, and always Column6 and Column7, you only need Table.AddColumn(tbl, "new_column_name", each ([Column6] ?? 0) + ([Column7] ?? 0)). This is to avoid the second situation mentioned above.

[frenchy988]

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"

[ToddChitt · Accepted solution]

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 ?