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 7 months, 2 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 8 months ago by  pietlinden.
• This reply was modified 8 months 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 8 months ago by  pietlinden.

Phil,

Not sure I totally answered your question before... To make a long story short(er), I'm basically trying to see/analyze/understand how profitable the business is over time (with the crazy grain...

This design works assuming that you may want to add the costs of a product's ingredients, but only ever want to aggregate the selling price of products by using an...

but only ever want to aggregate the selling price of products by using an average across time...or the latest value which is similar to what you would have done if...

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

Martin,

I think I understand your answer. (Took a couple days!) Basically, the ProductIngredient table (ProductID, IngredientID, Weight...) factless fact handles the slowly changing dimension by pointing each new version of...