How to generate date table based on other table period information and including daily value

[janihenr]

Hello,

I'm aiming to develop a daily revenue table.

I have an order item level table which includes invoice period start and end dates and calculated daily revenue value. I would like to create another table on Power Query level which would include calculated daily revenue on a daily and sales order level. Probably this is doable with functions but I'm not too familiar with it.

Source data looks something like this

The new table would include date (dates of each row from periodStart to periodEnd), daily revenue and salesorderItemId.

Any tips how to create this table for all sales order item rows for the whole invoicing period for each of the salesorderItem rows?

br,

Jani

[janihenr · Accepted solution]

Ok, I managed to find the solution. It is as simple as this:

{Number.From([invoicingPeriodStarts])..Number.From([invoicingPeriodEndDate])}

and then simply open the list.

There we go. 

[Anonymous]

Hello,

You can unpivot the two column. ( InvoicingPeriodEndDate, InvoicingPeriodStart). After that remove Attribute column.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRQ0lEyNtQ31DcyMAKxkZlGxkqxOtFKRvjVmIAVGYN4Rpb6RghFcKaJqRlYjQkeNeYWlkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Revenue = _t, #"End Date" = _t, #"Start Date" = _t, Item = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Revenue", Int64.Type}, {"InvoicingPeriodEndDate", type date}, {"InvoicingPeriodStart", type date}, {"Item", Int64.Type}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Revenue", "Item"},"Attribute" , "Date"),
#"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
#"Sorted Rows" = Table.Sort(#"Removed Columns",{{"Date", Order.Ascending}})
in
#"Sorted Rows"

[janihenr]

Hi,

Thanks for the reply.

I do get the start date and end date as a rows but what I need is all the rows in between as well. So this wasn't exactly what I am looking for. I did found an easy solution though. 

[Anonymous]

@janihenr 

I am a bit confusing, can you show the expected output based on the above 4 rows image table. 

Regards
Paul

[janihenr]

Hi Paul,

Thanks for asking more clarifying example. Hope the image below illustrates better what I am aiming for:

To me it currently looks like I should extract these tables as physical tables with the ETL tool instead of Power Query due to performance reasons. 

Naturally one option is just to build a measure with proper filters. However, I feel the performance may also become an issue with a larger data set with this approach.

Any recommendations of preferred solution or ideas would be interesting to hear.

Many thanks,

Jani

[janihenr]

I managed to create a temporary workaround for small dataset by first creating a cross join with my 11 year long date table and then filtering out unnecessary rows. The better solution should create the correct dates based on the periodStart and periodEnd fields.