November 16, 2024 at 9:01 pm
I'm using PowerQuery in Excel on my desktop. (Yeah, I know you can do this in dataflows if you want to spend a lot of money in Fabric, but I want to do it on my desktop). I have a working PowerQuery that basically reads and transforms data (oh really, is that what they do???!). But I want to write that to my database. I can import from the Excel file, but (1) how do I force a Refresh in code so that I have the latest version of all the data? The other downside about PowerQuery is no writer methods.
In Python, I can write the contents of a pandas dataframe to a file no problem.
with open('countries.csv', 'w', encoding='UTF8', newline='') as f:
writer = csv.DictWriter(f, fieldnames = fieldnames)
writer.writeheader()
writer.WriteRows()
Can I use Python or R to read the contents of a PowerQuery result (table) and use something like the above to write the contents of the dataframe (or whatever the internal data structure thing is in PowerQuery) to a file? Or is that only baked into Fabric? Ideally, I'd like to write to my database, but I tried that mess with SSIS and the PowerQuery source and it was an epic fail. There were no named columns in the output. So I'm looking for pretty much any way to do it.
If I do the PowerQuery transforms and land the data in Excel, I can do it, but how do I call a Refresh of the PowerQuery in SSIS if I do that?
PowerQuery - the almost game changer. =(
November 17, 2024 at 1:39 am
Apparently Imke Feldmann wrote some code that does it... but by the looks of it, you have to configure everything just right:
Export data from Power BI using Python
"Re-search. It means 'look again', don't it?"
--Kurt Vonnegut, Cat's Cradle.
November 20, 2024 at 10:20 pm
To quote Gene Wilder in "Charlie and the Chocolate Factory", "Wait a minute, Strike that! Reverse it! Thank you."
The trick is to avoid SSIS altogether. If you have DAX Studio installed (and who would use PowerBI without it?), then you just click on your query and then choose Export Data and then specify your SQL Server instance and database. Super tricky, that!
Yeah, "here's your sign". Thanks. I needed a big sign.
But is there a way to automate that? I'd like to import the contents of that file into a staging table. Then I can detect changed values etc, and create slowly changing dimensions from that.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply