Forum Discussion

anshgupta's avatar
anshgupta
Frequent Visitor
3 years ago
Solved

Add rows based on a column's first value

Hi,   I have a table in the form: Week Total 44 123 45 245 46 456   I want to add 3 rows in the table containing the last three weeks before week 44 shown in the first row abo...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi anshgupta ,

    Please try below steps:

    1. below is my test table

    Table:

    2. create a new table with below dax formula

    Table 2 =
VAR min_val =
    MINX ( ALL ( 'Table' ), [Week] )
VAR _a = min_val - 1
VAR _b = min_val - 2
VAR _c = min_val - 3
RETURN
    { ( _a, "" ), ( _b, "" ), ( _c, "" ) }

    3. create a new table with below dax formual

    Table 3 = UNION('Table','Table 2')

    Please refer the attached .pbix file.

     

    Best regards,
    Community Support Team_ Binbin Yu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

mangaus1111's avatar
mangaus1111
Icon for Solution Sage rankSolution Sage
3 years ago

Hi anshgupta ,

are 2 queries:

 

Table(2)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCk9NzVbSUQrJL0nMUYrViVYyMQFyDY2MIRxTIMcISII5ZkCOiamZUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Week", Int64.Type}, {"Total", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each [Week] -3),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Week", "Total"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Week"}}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns",{{"Week", type number}})
in
    #"Changed Type2"

 

 

Table

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCk9NzVbSUQrJL0nMUYrViVYyMQFyDY2MIRxTIMcISII5ZkCOiamZUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Week", Int64.Type}, {"Total", Int64.Type}}),
    #"Appended Query" = Table.Combine({#"Changed Type1", #"Table (2)"}),
    #"Sorted Rows" = Table.Sort(#"Appended Query",{{"Week", Order.Ascending}})
in
    #"Sorted Rows"

 If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.