Forum Discussion
Get value from prev column
Hi all
Stock inventory only updates once a year or when item stock level is changed, hence I get this type of table over a selected period
| Item/Week | 1 | 2 | 3 | 4 |
| Item 1 | 3 | 2 | 0 | |
| Item 2 | 2 | |||
| Item 3 | 5 | 4 | ||
| Item 4 | 3 | 0 |
But what I want is this
| Item/Week | 1 | 2 | 3 | 4 |
| Item 1 | 3 | 2 | 0 | 0 |
| Item 2 | 2 | 2 | 2 | 2 |
| Item 3 | 5 | 5 | 4 | 4 |
| Item 4 | 3 | 3 | 3 | 0 |
So how do I create a measure that checks if value is not there and if so use the previous column value,
The logic would be something like:
IF (Week 3 has value )
Show week 3 value
Else
Show week 2 value
/Markus
I believe we will have to add missing rows for weeks first.
Then we can use a DAX measure to get last non blank value or we can directly do it in Power Query As well
Please see the attached file and let me know if it helps
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8ixJzVUwVNJRAmFjpVgdqJAxVMgUIQTiGoExXMgIU8gEKmSMqhFkngGq8SBsgqrRBKIqFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Item = _t, Week = _t, StockQty = _t]), Custom1 = List.Distinct(Source[Item]), Custom2 = List.Distinct(Source[Week]), Items=Table.FromColumns({Custom1,List.Repeat({1},List.Count(Custom1))}), Weeks=Table.FromColumns({Custom2,List.Repeat({1},List.Count(Custom2))}), MergedQueries = Table.NestedJoin(Items,{"Column2"},Weeks,{"Column2"},"mytable",JoinKind.LeftOuter), Expandmytable = Table.ExpandTableColumn(MergedQueries, "mytable", {"Column1"}, {"Week"}), RemovedColumns = Table.RemoveColumns(Expandmytable,{"Column2"}), MergedQueriesAgain = Table.NestedJoin(RemovedColumns,{"Column1", "Week"},Source,{"Item", "Week"},"Table",JoinKind.LeftOuter), #"Expanded Table" = Table.ExpandTableColumn(MergedQueriesAgain, "Table", {"StockQty"}, {"StockQty"}), #"Sorted Rows" = Table.Sort(#"Expanded Table",{{"Column1", Order.Ascending}}), #"Renamed Columns" = Table.RenameColumns(#"Sorted Rows",{{"Column1", "Item"}}) in #"Renamed Columns"
3 Replies
Hi Markus_74
Please see if this approach helps
File attached as well
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8ixJzVUwVNJRMgZiIyA2AGIFpVgdqJQRmAuRUkCVAukwhQqboEqZQIWNobSBUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Item/Week" = _t, #"1" = _t, #"2" = _t, #"3" = _t, #"4" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Item/Week", type text}, {"1", Int64.Type}, {"2", Int64.Type}, {"3", Int64.Type}, {"4", Int64.Type}}), #"Transposed Table" = Table.Transpose(#"Changed Type"), #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]), #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Item 1", Int64.Type}, {"Item 2", Int64.Type}, {"Item 3", Int64.Type}, {"Item 4", Int64.Type}}), #"Filled Down" = Table.FillDown(#"Changed Type1",{"Item 1", "Item 2", "Item 3", "Item 4"}), #"Filled Up" = Table.FillUp(#"Filled Down",{"Item 1", "Item 2", "Item 3", "Item 4"}), #"Demoted Headers" = Table.DemoteHeaders(#"Filled Up"), #"Changed Type2" = Table.TransformColumnTypes(#"Demoted Headers",{{"Column1", type any}, {"Column2", type any}, {"Column3", type any}, {"Column4", type any}}), #"Transposed Table1" = Table.Transpose(#"Changed Type2") in #"Transposed Table1"Interesting solution
You are moving the solution to the data retrieval part
I thougt it would be easier to acheive this in the visualisation rendering
Also my raw data table does not look like the one above, that is more how my visualization looks like (I use the matrix visual)
This is the example of my raw data
Item Week StockQty Item 1 1 3 Item 3 1 5 Item 1 2 2 Item 2 2 2 Item 4 2 3 Item 1 3 0 Item 3 3 4 Item 4 4 0 Would that change the solution to the problem?
/Markus
I believe we will have to add missing rows for weeks first.
Then we can use a DAX measure to get last non blank value or we can directly do it in Power Query As well
Please see the attached file and let me know if it helps
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8ixJzVUwVNJRAmFjpVgdqJAxVMgUIQTiGoExXMgIU8gEKmSMqhFkngGq8SBsgqrRBKIqFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Item = _t, Week = _t, StockQty = _t]), Custom1 = List.Distinct(Source[Item]), Custom2 = List.Distinct(Source[Week]), Items=Table.FromColumns({Custom1,List.Repeat({1},List.Count(Custom1))}), Weeks=Table.FromColumns({Custom2,List.Repeat({1},List.Count(Custom2))}), MergedQueries = Table.NestedJoin(Items,{"Column2"},Weeks,{"Column2"},"mytable",JoinKind.LeftOuter), Expandmytable = Table.ExpandTableColumn(MergedQueries, "mytable", {"Column1"}, {"Week"}), RemovedColumns = Table.RemoveColumns(Expandmytable,{"Column2"}), MergedQueriesAgain = Table.NestedJoin(RemovedColumns,{"Column1", "Week"},Source,{"Item", "Week"},"Table",JoinKind.LeftOuter), #"Expanded Table" = Table.ExpandTableColumn(MergedQueriesAgain, "Table", {"StockQty"}, {"StockQty"}), #"Sorted Rows" = Table.Sort(#"Expanded Table",{{"Column1", Order.Ascending}}), #"Renamed Columns" = Table.RenameColumns(#"Sorted Rows",{{"Column1", "Item"}}) in #"Renamed Columns"
