Forum Discussion
Get value from prev column
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"
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"