Forum Discussion

kzmanus's avatar
kzmanus
Icon for Advocate I rankAdvocate I
8 years ago
Solved

Converting a Summary Table to Transaction Table

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 w...
Show and Tell
  • AlbertoFerrari's avatar
    AlbertoFerrari
    8 years ago

    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

MarcelBeug's avatar
MarcelBeug
Icon for Community Champion rankCommunity Champion
8 years ago

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"