Get value from prev column

[Markus_74]

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

[Zubair_Muhammad]

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"

[Markus_74]

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

[Zubair_Muhammad · Accepted solution]

@Markus_74 

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"