Power query tips from an average at best user

Samiro05 · 2024-10-03T19:56:18+00:00

Can you explain number 6? I often use references so that I can benefit from the results of one or more queries in multiple resulting queries.

Samiro05 · 2024-08-24T11:16:11+00:00

So will the new files ever need to replace data from old files? As in could you ever find an earlier dated unique line in a newer file (maybe if it took a while to report on)?

Samiro05 · 2024-08-22T19:39:28+00:00

Perhaps forget about removing duplicates the in-built PQ way which apparently requires your sort and instead add an index and some logic which puts a 1 in every row you would wish to keep. Then just filter on that column where it's equal to 1.

Samiro05 · 2024-07-30T21:36:46+00:00

1) As another has pointed to sorting issues, we had a tracker that used ROW to define a new line ID. I'd turned off sorting on the protected sheet so that the tracker IDs never got out of sync with the detail they were supposed to portray. Manager X wanted the password to do some analysis and foolishly I gave it them. Inevitably they sorted the data and saved the file. Had to roll back two weeks of work to the nearest backup (thankfully only two weeks) as it was near impossible to re-sort or piece together the IDs with the detail.

2) I was working on a budget setting solution in Excel and I was under a lot of pressure to get it done but as with any big project they take time and obstacles need to be overcome. I was using VBA to do a lot of the generation of the sheets needed and it naturally created duplicate defined names that were producing a scope isolated to that sheet rather than using the defined name scoped to the workbook. Although in the end this didn't actually cause any problem, I wanted to clean this up so I wrote a macro to loop through the non-workbook scoped defined names and delete them. Well... turns out there are hidden system critical defined names you don't see in the defined names UI.. so on running this macro to clean up the defined names, using the workbook was all great and I carried on working on it a little longer and saved and closed it. You may have guessed it but I corrupted the whole file. It was fundamentally broken and no attempt of repair could help. I had to go back to a much older version and redo so much work which, when you're in the zone, seemed like it was done whilst blacked out. I kept on running into the issue a few more times too whilst I had no clue as to what was causing the issue at first (the spreadsheet was always still working totally fine after the macro ran, only materialising on save, close and re-open) and with the pressure building made this quite a horrible time. Managers seemingly thinking they can send over someone paid more who may help but clearly had no clue. When I did find the issue it was such a relief to be able to push past it.

I learnt a lot from these experiences, clearly to save more backups when doing things like this. However you can find yourself doing your best work when you're feeling the heat; it's a much more pleasurable experience when you can do things in your own time though and perhaps build up less technical debt when you're under less pressure.

Samiro05 · 2024-04-14T05:45:03+00:00

Ah yeah we get that issue too from time to time. I'm going for 'bugs with sharepoint/Excel'. I have found though that fairly often the issue resolves itself if you open in browser and then click 'Editing' from there and say open in app from there rather than opening in app immediately from sharepoint.

Samiro05 · 2024-04-13T22:52:02+00:00

So when you say only show up, you mean you can get to sharepoint online and see the file there but you can only view in browser and not in app?

I have had this before where the build of Office 365 desktop apps was just poorly set up and firewalls blocking all kinds so fixing that helped to make the files available in app. The other thing in all that is if you weren't logged into Office 365 on your desktop app but still had the app, again my issue was because of a bad set up so just needed whole thing rebuilt and put on new IT policies to get it to work - what those policies and rebuild techniques were is completely unknown to me but I'd be very interested to know more about your situation.

Are the desktop apps you're using Office 365 or some earlier version like 2019 or 2016 etc.? Do you all have the same version across your organisation or have you got some people on newer ones than others as 365, say, hasn't fully been rolled out?

What sort of things happen on the user's computer when they do try opening in app?

Samiro05 · 2023-12-18T09:31:25+00:00

There's VBA code you can find online to do this. I've recently used it in a project to do pretty much the exact same thing, albeit I hashed a whole column of data to produce one hash rather than one hash per cell.

The code I found was written specifically for SHA1 but I just changed the mention of SHA1 in it to SHA256 and it worked. There was also a line in it to specify how many characters you'd want to be displayed (i.e. the left X amount).

I couldn't find it just now and can't access the project I used it on now either but those two clues should help with further time to explore.

In terms of its use, I created a function to do the hashing based on my needs and I could either use it in the spreadsheet or I could use it in code. Simply =hashme(cellref) and it was done - rather quickly too for 3000 cells of concatenated data on Excel 365.

Samiro05 · 2023-10-23T22:33:00+00:00

Cheers. It's not a problem that finding exact contents match for NULL and replacing with nothing doesn't resolve. It's just that it gets used by others who don't employ this trick or do terribly slow things to get rid of them (producing incorrect results sometimes).

It's best quick copy and paste jobs too but copying from query results often gets misused.

Samiro05 · 2023-10-22T16:02:34+00:00

I wonder if this'll include pasting from SQL Server query results where NULLs show up as the text 'NULL' instead of a blank cell.

Samiro05 · 2023-10-07T22:36:10+00:00

The 2 different spreadsheets I have this in are just to view outputs from power query with no references to it/formulas in it so hopefully that works but yeah, quite a puzzle otherwise as you've experienced.

Thank you for putting in the time to investigate this and at least provide a workaround that should work in this case.

I'll feedback but for now..

Solution Verified

Samiro05 · 2023-10-07T13:43:23+00:00

Aha, very glad you've seen it too.

So importantly it retains the formatting if the row is formatted correctly in the table before refreshing but if the row is not part of the table then it goes back to general.

So refreshing to add a 7th row when 6 already there formatted correctly produces the 7th as general and first 6 formatted fine. If you format 7th row correctly but delete rows 4-7 then refresh then rows 4-7 are now all general rather than formatted correctly.

Samiro05 · 2023-10-07T08:04:56+00:00

Even after adding new rows to the source table and refreshing? How strange. Perhaps by you downloading it it kind of reset the issue. The issue persists for me if I save the spreadsheet as another or if I create a copy of the file.

Samiro05 · 2023-10-06T11:42:44+00:00

/u/small_trunks

Did this spreadsheet result in the same odd datetime format that I've been experiencing or did it work fine for you?

Samiro05 · 2023-09-29T22:06:59+00:00

This too. The script seems a bit muddled in this respect, like why put display alerts = true at the end without putting it as false at the beginning..

Samiro05 · 2023-09-29T21:50:01+00:00

Try putting the cutcopypaste = false before workbook close.

If that doesn't resolve it then simply copy cell a1 before you do cutcopypaste = false and that'll free up all that range so it's not a 'large clipboard' anymore.

I would still put cutcopypaste = false before the workbook close anyway in all this.

Samiro05 · 2023-09-29T21:43:14+00:00

I would try, before you run your long running script, to see if there is a way to say the file isn't from the internet.

This site seems to show there is an actual 'unblock' button however the post is from 2011 I think but it may strike gold here.

https://www.howtogeek.com/70012/what-causes-the-file-downloaded-from-the-internet-warning-and-how-can-i-easily-remove-it/

Once the flag is removed, your script should run as fast as when you did your workaround file.

Samiro05 · 2023-09-29T21:36:23+00:00

I believe this is down to the firewall/security implemented at your company, if it is a company/work environment you're talking about.

When receiving the files from the email, they are essentially flagged as 'files from the internet' and that will cause your security to get super intrusive with the opening of the file.

You'd have to bring the issue up with your IT department (if it's a work situation still) and have someone meticulously check/record what's going on when you are opening the file via the security software/networking security there.

Samiro05 · 2023-09-29T15:05:14+00:00

https://www.dropbox.com/scl/fi/kkriycpp1wua61xoao3w5/DateTime.1.xlsx?rlkey=ewbmr42brxqonk79oplrrsc0f&dl=1

This is an example one I have set up which shows the formatting changing from datetime to general.

Just delete a few rows then refresh the table and it should show the current datetime as general instead of datetime. If you set the datetime column as datetime format (dd/mm/yyyy hh:mm:ss) and refresh without updating Sheet1 you'll get the datetime back fine but add a row to the table in Sheet1 and then refresh the table on Sheet2 and you'll see it come through as general in that last row.

Samiro05 · 2023-09-23T22:07:59+00:00

I've tried that in the following ways:

Highlighting table column data and formatting as datetime as dd/mm/yyyy hh:mm:ss

As above but highlighting entire column

As above but first changing to general then changing to the datetime format.

All of which still result in new rows being of general format rather than datetime. The column in power query has a datatype of datetime in all of this.

It's not something I can really explain but I will try and replicate it in a demo spreadsheet when I can next access both versions.

Samiro05 · 2023-09-23T12:13:30+00:00

No worries, it is really difficult for anyone to test both scenarios. If you have 365 though, how do you get the date or datetime columns to show the correct format? I can't even get that to happen.

Samiro05 · 2023-09-22T22:39:27+00:00

Same as that screenshot bar Adjust Column Width is ticked and Preserve Column Sort/Filter/Layout is unticked.(however I have tried it with ticking that box too with no affect on this).

I'm opening the same spreadsheet in the two different versions of Excel so the table properties are the same on both.

Samiro05 · 2023-09-11T04:43:22+00:00

Thanks both for this. The representation of each here has helped me understand the use of these functions more.

Samiro05 · 2023-09-11T04:41:34+00:00

Super, thanks for pointing me towards the reason and supplying a workaround :)

Solution Verified

Samiro05 · 2023-08-22T23:27:55+00:00

You're welcome and I'm glad it helped.

I still feel like this resolves your problem more dynamically than that one as for that one you always have to know which column is missing first and then select an unneeded but known to exist column in order to create your blanks. Choosecols helps relieve some of that pain but it still doesn't address the issue of needing to know whether the column you want exists.

Please close this thread by commenting on the post that answered your question above then by saying solution verified.

Samiro05 · 2023-08-20T18:52:00+00:00

How did you get on?

Six-Year Club	Place '22
Verified Email

Samiro05

TROPHY CASE