Converting a Summary Table to Transaction Table

[kzmanus]

Would like someone to tell me how to accomplish this. The No. Days Rented column would act as a counter for each row and add the appropriate number of rows to transaction table and update date by 1 with starting date = Start

  

[AlbertoFerrari · Accepted solution]

Hi Kevin,

another option is to leverage GENERATE, like in:

Transactions = 
GENERATE ( 
    Equip,
    CALENDAR ( 
        Equip[START], 
        IF ( 
            ISBLANK ( Equip[RETURN DATE] ), 
            TODAY (), 
            Equip[RETURN DATE] 
        )
    )
)

The drawback is that you will have both tables in the model (the base table and the expanded one), the good is that it gives you a lot of flexibility. It all depends on the usage you need to do with the table.

Have fun with DAX!

Alberto Ferrari
http://www.sqlbi.com

[kzmanus]

Agreed. To bad Power BI doesn't have the connection only setting excel does for power query. Wonder if we can do the summary table in memory?

[MarcelBeug]

You can add a column with a list from 0 tru the number of days, transform this list and add those numbers to the START date, using Date.AddDays.

After the code is generated, you need to adjust the first "each" to (e.g.) ThisRow => and add this parameter to the field names in your formula.

Then you can expand the column and replace the original START column with the new column.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WstC31DcyMDRX0lEyVYqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [START = _t, #"No Of Days Rented" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"START", type date}, {"No Of Days Rented", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "DATE", (ThisRow) => List.Transform({0..ThisRow[No Of Days Rented]}, each Date.AddDays(ThisRow[START],_)), type {date}),
    #"Expanded DATE" = Table.ExpandListColumn(#"Added Custom", "DATE"),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded DATE",{"START", "No Of Days Rented"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"DATE", "START"}})
in
    #"Renamed Columns"