Forum Discussion
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 above. So,
the new table should look like:
| Week | Total |
| 41 | |
| 42 | |
| 43 | |
| 44 | 123 |
| 45 | 245 |
| 46 | 456 |
The first row of the table at the top will not always be 44. So, the solution needs to be dynamic in the sense that if the first row was 35, then the three rows added would be 32,33,34. I am having a hard time figuring out how to achieve this in power bi. Can somebody please provide any insight?
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.
4 Replies
Hi anshgupta ,
see my pbi file. I have done it power query
https://1drv.ms/u/s!Aj45jbu0mDVJi1EPUFGFoDQKktRZ?e=Ko2Pu3
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
I am not able to access the link
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.
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.
