Tips & Tricks
Are you looking for some of the best-known Tips & Tricks or have some to share. This is the place to... View more
Organizer:
- Organized by
-
-
Best “unintended” custom tool feature?
-
Best “unintended” custom tool feature?
The estimating software that we use, B2W (formerly known as “Bid 2 Win”), has a very specific Excel column order. When I created my custom tools I created custom columns for each of the 9 required import columns so they would all be present, in the correct order in my CSV export file, simple enough. Where it gets interesting is that one of the columns is “ignored” by the software, but it still needs to be there when you upload the data to the software.
Knowing what I know now (thank you @Doug McLean !) I could simply add the majority of the columns during my data washing using Power Query since most of them are left blank at the takeoff stage. What I’ve found since then though is the mysterious “N/A” column can be very useful as a sorting/grouping tool. Most recently I’ve used it to split items between multiple phases of the project by simply establishing “Phase 1, Phase 2, Phase 3, etc.” values in the “N/A” column.
Basically I built in an additional sorting option completely by accident. 🙃
Have you found any “unintended” features like this in any of your tools?
-
5 Replies
-
Points: 26,924Rank: UC2 Brainery Advanced Brown Belt
This is great @David Cutler. I’ve built tools and custom columns for customers that match the estimating software for import. Sometimes the data in those columns is empty in Bluebeam, like you said. I simply hide those columns in Bluebeam to reduce clutter. They can still export, but we don’t need to see them. Unless, of course, you’re adding value by using them differently, like you are.
Great post, thanks for sharing!
-
Points: 30,991Rank: UC2 Brainery Advanced Brown Belt II
One of the advantages of using this “ignored” column is that you don’t have to clear the information out of it when creating your upload file since the software is going to ignore it anyway… 😀
-
-
Points: 17,697Rank: UC2 Brainery Advanced Blue Belt II
I almost always build tools with intent, and I try to work out what’s going to happen before it happens, that being said… best laid plans and all that. 💥💣
When this does happen its usually much later down the road when I realize I can use an existing data point for something else. I’ve had a couple of instances where 💡and I say, “hey, I can use this for a join or something.
-
Points: 17,697Rank: UC2 Brainery Advanced Blue Belt II
Its actually not important at all to export your custom columns in the correct order as you can set a final column order in PQ as one of your last steps.
Table.ReorderColumns is the command.
The way I would handle this is to have a Connection Only query of the final order saved as a List, and then use the Table.ReorderColumns command with that list to set your final order.-
Points: 30,991Rank: UC2 Brainery Advanced Brown Belt II
I agree 100% @Doug McLean
Some of my workflow was developed before I had basic understandings of Power Query so having the required columns, in the right order saved steps in my manual data washing process. Not knowing what I didn’t know about Revu and Power Query I built very basic tools. While I know more now, I still aim to keep things relatively simple so that they are easy for others to understand… 😎
-
Log in to reply.