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 ?
Super UserThe 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.