Forum Discussion
How to perform excel data query with multiple changing conditions
Added the following step between the Source & SelectedTodaysFolder steps:
NoSystemHiddenFile = Table.SelectRows(Source, each ([Attributes]?[Hidden]? <> true) and ([Attributes]?[System]? <> true) ),Not tested but that should solve the issue - let me know
The only thing I noticed was if there was an autosave file that is the same number of characters length that was queried. Is there a way to only query "real" files? For example, ~$05 Jan 2024 Tasking List.xlsx which is 26 + 5 (31) characters which is the same as 05 Jan 2024 Retasking List.xlsx which is 26+5 (31) characters.
I can definitely work around it and add a Text.Contains for "Retasking" vs "Tasking" but I wanted to see if you knew how to get around autosave or hidden files?
Thanks!
Added the following step between the Source & SelectedTodaysFolder steps:
NoSystemHiddenFile = Table.SelectRows(Source, each
([Attributes]?[Hidden]? <> true) and
([Attributes]?[System]? <> true)
),Not tested but that should solve the issue - let me know
marshalltj67 Glad I could help you & Thanks for your feedback
- I understand! I am working this code on a different stand alone system so it is hard paint the entire picture.
It looks like when I run a query and the output updates my columns, my assigned names in the name manager update and if a new column is added, that column remains "Column #" so all I have to do is assign a new name so I should be good to go!
I appreciate the help and my apologies for the delay! Hi marshalltj67
Obviously I don't have the complete picture (don't know what you other query does) and consequently don't understand why you need to name the columns in the Name Manager...
Re. This should be fine as long as all the columns remain the same. I am not sure if the assigned "name" for the column will update in the "Name Manger" if the query outputs another column
Answer is clearly No. A query doesn't change/update anything in the Name Manager
When you have time and to help people who Search it would be appreciated you Mark as solution the post from Jan 06 2024 06:54 PM with attached file PQ3_marshalltj67.xlsx. With a few tweaks this could be re-used - Thanks
Hi Lorenzo ,
By name manager I meant "Define Name" source that you get to by going to "Formulas" then "Name Manager".I was able to name all the columns via this name manager function by selecting all the columns, then clicking "Create from Selection" and then naming the column off the top row.
This should be fine as long as all the columns remain the same. I am not sure if the assigned "name" for the column will update in the "Name Manger" if the query outputs another column.
Thanks!
Hi marshalltj67. Coud you post an update please?
Do you know if it is possible to name all the columns utilizing the name manager?
If by 'name manager' you mean Power Query Editor, yes you can rename column(s) in two ways:
- Double-click on a column header > Enter the desired name
- Right-click on a column header > Rename... > Enter the desired name
That said I'm not sure this is what you're after and TBH I'm not sure I understand: so I wanted to be able to reference "Tasking Date" as a named column just in case the column gets shifted
If renaming the columns doesn't do what you expect you'll have to find a way (pictures ???) to re-explain me what the potential issue is - Sorry
- That worked perfectly! Thank you!
I think I am almost there with the final query... Do you know if it is possible to name all the columns utilizing the name manager? For example if an output column from my query is "Tasking Date" is there a function I can use to save that column (A:A) as "Tasking Date"?
I was able to get my script to query all the data and then I promoted the headers and then used Text.Trim to clean up the column names and data within the columns but I will be referencing the data in another sheet so I wanted to be able to reference "Tasking Date" as a named column just in case the column gets shifted.
Thanks! I greatly appreciate all your help!