<checking calendar>

Nope, still 2024.

Drat!

Okay, thanks for looking at this. Thought I was just missing something. Maybe I'm not. The PowerQuery source just isn't nearly as functional as I had hoped it would be....

• This reply was modified 4 months, 3 weeks ago by  pietlinden.

I think the PQ Source is a dead end. It's crazy buggy. Either that or I'm doing something incredibly stupid in SSIS, because if I use the PQ Source, SSIS...

Not 100% sure the destination matters at this point. I can't get the PowerQuery Source to output anything at all. If I use an Excel source (that runs a PowerQuery),...

Okay.. I got things working so far... not quite what I want, but closer:

Okay... progress, of a sort.

Excel Source

to

Derived Column (unit price)

to

Data Conversion (Convert PQ and Excel Types to SQL...

I tweaked my code because I wanted to be able to call the PowerQuery function that reads the PDF once for each file... here's the code (Note: this works perfectly...

• This reply was modified 4 months, 3 weeks ago by  pietlinden.

Just wondering... can I put the PowerQuery source inside a For Each File Loop, and then, if I make the PowerQuery a variable, basically do a REPLACE (Yes, I know,...

DECLARE @TextToParse VARCHAR(100) = 'for col1, for col2, for col3, for col4';

INSERT INTO #SomeTable(SplitValue)
SELECT TRIM(value) AS SplitValue
FROM string_split(@TextToParse,',');

is one way

This is absolutely a post which would benefit immensely from a sample record or two.

I see no reason why you can't use something like DelimitedSplit8K or SPLIT_STRING() to separate the...

oh, the 25 minutes. that's the giveaway. use LAG, like I said.

use tempdb;
go

CREATE TABLE ds(
ID INT NOT NULL,
Flag BIT,
EventTime DATETIME2
);
GO
INSERT INTO ds VALUES
(10,0,'2024-06-24 14:20:19.437'),
(11,1,'2024-06-24 14:45:45.027'),
(12,0,'2024-06-24 14:47:35.067'),
(13,1,'2024-06-24 15:14:45.653'),
(14,0,'2024-06-24...

use LAG()? I tried to use your picture to create a table, but SSMS didn't know what to do with the picture when I tried to paste it in.

In all...

After fiddling with this for what seems like forever, I finally got most of the way there. The PowerQuery transform works, but the data type transformations do not.  I inserted...

Okay, I got it to work sort of.   Had to resort to using Excel to run the PowerQuery on a folder source, filter for PDFs I want, and then once...

• This reply was modified 5 months, 1 week ago by  pietlinden.
• This reply was modified 5 months, 1 week ago by  pietlinden.

I think I understand how to do it now. Running totals of Sales Amount (Gross Revenue) is straightforward. With the SCD2 of Product, I can store the unit cost of...

I haven't touched SSRS in forever  (looks in book). You *might* be able to use RunningValue(), but I would do this in T-SQL as it's much easier to do there....

• This reply was modified 5 months, 1 week ago by  pietlinden.