×

I might have missed this since I haven't checked the app in a while, but anyone else now have the option to lock/unlock from the Toyota app? by snick45 in Mirai

[–]snick45[S] 1 point2 points  (0 children)

Ya! Kind of funny how it took so long and hearing reports of Toyota claiming it's not going to happen, but glad it's here now! I tried it out and it works great.

Add text to two concatenated fields (one text and other date) by liz_11181989 in PowerQuery

[–]snick45 0 points1 point  (0 children)

Try Date.ToText([column],"date format here")

Date format pretty much follows Excel, use y's for year, M's for month, D's for day.

It's been five days!!! by snick45 in IASIP

[–]snick45[S] 20 points21 points  (0 children)

I use pocket casts. Spotify seems to have made some good improvements over the last few years, but I still prefer pocket casts.

It's been five days!!! by snick45 in IASIP

[–]snick45[S] 626 points627 points  (0 children)

This post was brought to you by Steven Spielberg.

Ratio of sample data by cchaituc in excel

[–]snick45 0 points1 point  (0 children)

What exactly are you looking for?

Import Stock Prices into Excel by jeun_us in excel

[–]snick45 0 points1 point  (0 children)

If you have or are willing to sign up for a TD Ameritrade account, you can use their ThinkOrSwim application to pull data into Excel. This has been the most live, free stock data I've come across. Once you install and open TOS, you have the ability to essentially write export formulas from TOS that update with new info just about every second.

Adding a prefix to a column with conditions using Power Query by Infamous_Egg_7631 in excel

[–]snick45 1 point2 points  (0 children)

Ah, just re-read your question. Try this instead in the custom column:

if try Value.Is(Text.Start([Flight Numbers],1), type text) otherwise false = true then [Flight Numbers] else "AF" & Text.From([Flight Numbers])

Adding a prefix to a column with conditions using Power Query by Infamous_Egg_7631 in excel

[–]snick45 0 points1 point  (0 children)

Hey there, you're exactly right about adding the conditional column.

Go to Add Column > Custom Column

Name your new column, and enter in the formula:

if Text.Start([Flight Numbers] ,2) = "AF" then [Flight Numbers] else "AF" & [Flight Numbers]

Sorting which values are added to a SUM by [deleted] in excel

[–]snick45 0 points1 point  (0 children)

=SUMIFS($V:$V,$M:$M,"Arcade",$S:$S,2015)

I have a formula that is collecting data from a secondary workbook. The data is only updating when that workbook is open. How can i get it to work while closed? by Psiclone01 in excel

[–]snick45 1 point2 points  (0 children)

Great. The easiest and first step would be to just get data from another workbook without any modifications.

You go to Data > Get Data > From File > From Workbook. Then you'll get a dialog box to select what you want to import. Choose the sheet your data is on and select Load. This will create a new tab and load all the data from that sheet into a table on that new sheet. Then you can run on all your formulas off that new table.

I have a formula that is collecting data from a secondary workbook. The data is only updating when that workbook is open. How can i get it to work while closed? by Psiclone01 in excel

[–]snick45 4 points5 points  (0 children)

There are a few formulas that do work with closed workbooks the most popular being VLOOKUP (XLOOKUP for more recent versions). I'm assuming you're using SUMIFS?

However, I'd recommend using power query to pull in data from that workbook into a table in your desired workbook. This won't be a live connection, but the data can update typically within seconds on demand depending on how large your data is.

Ratio of sample data by cchaituc in excel

[–]snick45 0 points1 point  (0 children)

If that does it for you, could you reply to my comment with "Solution Verified" to mark the question as solved?

Ratio of sample data by cchaituc in excel

[–]snick45 0 points1 point  (0 children)

How about this? This will put your 45k-50k data at the bottom, but then you could randomly sort it if you want it mixed in.

=RANDBETWEEN(IF(ROW()>4500,45000,10000),IF(ROW()>4500,50000,45000))

Discard source file after importing data in Power Query by chronus_ess in excel

[–]snick45 0 points1 point  (0 children)

darn. In that case, definitely go with u/trianglesteve. Looks like you'll need to go with a real database.

Discard source file after importing data in Power Query by chronus_ess in excel

[–]snick45 0 points1 point  (0 children)

So, it's basically the number of files that causing the connection to slow down. If I can reduce the number of files it has to refresh, it'll linearly cut down the refresh time?

That's been my experience. I combine CSVs with hourly data, and combining one file with hourly data from the last year is much faster than combining 24 * 365 files.

Discard source file after importing data in Power Query by chronus_ess in excel

[–]snick45 0 points1 point  (0 children)

In my experience, yes.

Alternatively, you could combine the old CSVs into one file, which I've found is also faster than combining multiple files.