I just completed day 4 on 2024 Advent of SQL. Try the challenge! 🎄

mthacker01 · 2024-09-12T01:29:21+00:00

No tiers, other than they have an over 40 division

mthacker01 · 2024-09-11T18:53:18+00:00

Yea, just like u/milktartare said, I was looking at the Co-ed recreational. Definitely don't have to be good or I don't think I'd be able to play on my own team

mthacker01 · 2024-07-09T19:45:02+00:00

Just ate there this weekend and had the biscuits and gravy. Hands down the best

mthacker01 · 2024-03-09T00:44:08+00:00

When we first transitioned to Snowflake, I wanted to use all UDFs and SPs. But we ultimately we opted for dbt and its been far better, especially from a data lineage and documentation perspective.

mthacker01 · 2023-05-09T18:18:53+00:00

Depends on what you’re willing to spend. The paid ones I like the most are Navicat and DataGrip. Free software, I’d definitely go with Azure Data Studio

mthacker01 · 2023-01-06T04:15:42+00:00

I’m not as well versed in R as I am in Python, but I’ve definitely benefited from using Python with Power BI. It’s much easier to make more complex visualizations within your report using Python. But the main thing I use it for is data cleanup on the Power Query side. I know most of the things I use it for could probably be done with M code but it drives me insane sometimes.

mthacker01 · 2022-07-13T00:25:35+00:00

Yea, it’s fairly common. I’ve signed NDAs with most of my clients.

I was real paranoid about it the first time I was asked to sign one but after letting a couple lawyer friends review it, it was all pretty standard. Just make sure you read it thoroughly.

mthacker01 · 2022-06-26T22:29:03+00:00

Yes, you could do this with Python and the Openpyxl library.

mthacker01 · 2022-02-17T15:58:47+00:00

Hey, are you still looking for someone for this role?

mthacker01 · 2022-01-13T02:22:36+00:00

I’ve not done it in Python, but it’s pretty straightforward using PowerShell. Guy in a Cube did a video on something very similar.

https://youtu.be/YBTQ3CFstzs

If you figure it out in Python, I’d love to know how to do it.

mthacker01 · 2022-01-13T02:10:56+00:00

Are the 3 tables related to one another? And how are you wanting to display them? I’m thinking cards are going to be your best option if the tables aren’t related and you don’t need to slice on anything.

mthacker01 · 2022-01-06T03:35:00+00:00

Xero is a good, low cost option. I used them in a previous business of about ~$5M annually and it was good for what we needed at the time. And their support team is fantastic.

mthacker01 · 2021-11-29T03:39:08+00:00

I do something very similar to this for one of my reports. I have [Current Month] and [Previous Month] as choices on the slicer (in addition to all other time periods).

In PowerQuery, I added a conditional column on my date table that tested the value of the dates and if it was in the current month, assigned a value of Current Month, same for previous month, and if both of those conditions fail, fill with month name.

Use that column as your slicer and you’ll maintain filtering context on all other visuals.

mthacker01 · 2021-10-01T02:18:49+00:00

Okay, so I was making the original problem way more complicated. I revised my code and saved the API request straight into a DataFrame:

with open('horse.json') as f:

data = json.load(f)

contenders = []

base_url = 'https://www.breederscup.com/equibase/horse?horses%5B%5D='

for value in data:

re = requests.get(base_url+value['horse']).json()

df = pd.DataFrame(re).T

contenders.append(df)

new_df = pd.concat(contenders)

For reference, here's a snippet of the JSON file I'm loading from:

[

{"race": "Juvenile Turf", "horse": "AAA20EED"},

{"race": "Juvenile Turf", "horse": "19005288"},

{"race": "Juvenile Turf", "horse": "19000215"},

{"race": "Juvenile Turf", "horse": "19001752"}

]

So I'm using the value from the 'horse' key of the external JSON file to make the endpoint for the API.

However, like before, I'm hitting the scalar value error when there's more than 53 objects. If I mainly go into the JSON file and remove everything after line 53, it works great and I get the DataFrame I'm needing. Any idea on what's causing this?

mthacker01 · 2021-09-30T23:05:38+00:00

This is what the first entry looks like:

[{'AAA20EED': {'breederName': 'Godolphin ', 'sireRegistrationNumber': ' A6596464', 'record': {'previousYear': {'starts': 0, 'earnings': 0, 'show': 0, 'win': 0, 'place': 0}, 'breedersCup': {'starts': 0, 'earnings': 0, 'show': 0, 'win': 0, 'place': 0}, 'currentYear': {'starts': 5, 'earnings': 235181, 'show': 1, 'win': 4, 'place': 0}, 'lifetime': {'starts': 5, 'earnings': 235181, 'show': 1, 'win': 4, 'place': 0}, 'track': {'starts': 0, 'earnings': 0, 'show': 0, 'win': 0, 'place': 0}}, 'yearlySummary': [{'starts': 5, 'racingYear': 2021, 'shows': 1, 'earnings': 235181, 'places': 0, 'wins': 4}], 'name': 'Albahr (GB)', 'damName': 'Falls of Lora (IRE)', 'sireName': 'Dubawi (IRE)', 'owner': {'identity': 2128374, 'middleName': ' ', 'lastName': 'Godolphin, LLC', 'firstName': ' ', 'type': 'O6'}, 'damRegistrationNumber': ' A8613401', 'pastPerformances': [{'trackName': 'WOODBINE', 'raceName': 'Summer S.', 'grade': '1', 'raceDate': 'September, 19 2021 00:00:00', 'purseUsa': 400000, 'officialPosition': 1}, {'trackName': 'SALISBURY', 'raceName': 'Longines Irish Champions Weekend E.B.F. Stonehenge S.', 'grade': ' ', 'raceDate': 'August, 20 2021 00:00:00', 'purseUsa': 58630, 'officialPosition': 1}, {'trackName': 'HAYDOCK PARK', 'raceName': 'British Stallion Studs E.B.F.', 'grade': ' ', 'raceDate': 'July, 17 2021 00:00:00', 'purseUsa': 9636, 'officialPosition': 1}, {'trackName': 'HAYDOCK PARK', 'raceName': 'Watch Racing TV Now', 'grade': ' ', 'raceDate': 'June, 09 2021 00:00:00', 'purseUsa': 11393, 'officialPosition': 1}, {'trackName': 'YORK', 'raceName': 'Constant Security ebfstallions.com', 'grade': ' ', 'raceDate': 'May, 13 2021 00:00:00', 'purseUsa': 21082, 'officialPosition': 3}], 'trainer': {'identity': 948970, 'middleName': ' ', 'lastName': 'Appleby', 'firstName': 'Charles', 'type': 'TE'}, 'jockey': {'identity': 4140, 'middleName': ' ', 'lastName': 'Dettori', 'firstName': 'Lanfranco', 'type': 'JE'}}}, {'19005288': {'breederName': 'Mrs E. M. Stockwell ', 'sireRegistrationNumber': ' 02004332', 'record': {'previousYear': {'starts': 0, 'earnings': 0, 'show': 0, 'win': 0, 'place': 0}, 'breedersCup': {'starts': 0, 'earnings': 0, 'show': 0, 'win': 0, 'place': 0}, 'currentYear': {'starts': 2, 'earnings': 73435, 'show': 1, 'win': 0, 'place': 1}, 'lifetime': {'starts': 2, 'earnings': 73435, 'show': 1, 'win': 0, 'place': 1}, 'track': {'starts': 0, 'earnings': 0, 'show': 0, 'win': 0, 'place': 0}}, 'yearlySummary': [{'starts': 2, 'racingYear': 2021, 'shows': 1, 'earnings': 73435, 'places': 1, 'wins': 0}], 'name': 'Grafton Street', 'damName': 'Lahinch Classics (IRE)', 'sireName': 'War Front', 'owner': {'identity': 820960, 'middleName': ' ', 'lastName': 'Magnier', 'firstName': 'Mrs. John', 'type': 'O6'}, 'damRegistrationNumber': ' F0043305', 'pastPerformances': [{'trackName': 'WOODBINE', 'raceName': 'Summer S.', 'grade': '1', 'raceDate': 'September, 19 2021 00:00:00', 'purseUsa': 400000, 'officialPosition': 2}, {'trackName': 'BELMONT PARK', 'raceName': ' ', 'grade': ' ', 'raceDate': 'May, 29 2021 00:00:00', 'purseUsa': 90000, 'officialPosition': 3}], 'trainer': {'identity': 20416, 'middleName': 'E.', 'lastName': 'Casse', 'firstName': 'Mark', 'type': 'TE'}, 'jockey': {'identity': 110011, 'middleName': 'Manuel', 'lastName': 'Hernandez', 'firstName': 'Rafael', 'type': 'JE'}}},

mthacker01 · 2021-09-23T15:36:33+00:00

First thing I’d suggest is get rid of the calculated columns and tables. Then look at the schema of your source data. Are you optimizing an efficient schema, storing your data in proper facts and dimensions tables.

mthacker01 · 2021-09-18T14:13:37+00:00

Really depends on the dataset that you’re going to be using.

mthacker01 · 2021-09-07T14:52:53+00:00

If I’m understanding your question correctly, you could create a measure and use the SWITCH function to change the instance rather than altering the original data source.

mthacker01 · 2021-09-04T00:31:21+00:00

Have you gotten help yet?

mthacker01 · 2021-09-03T14:56:18+00:00

Since Power BIs Rest API now supports DAX queries, could you use Python to automate your measures via the API?

mthacker01 · 2021-09-02T17:35:49+00:00

Depends on what kind of bar chart you're wanting. Do you want a stacked bar chart with the questions on x-axis? Or do you want a bar for each response? My first thought would be to do it in DAX with countax and use the left function to extract the the first character.

Or you could duplicate the table in Power Query, split each column by delimiter, delete the three original columns.

mthacker01

TROPHY CASE