Power Query column to identify missing rows + criterion

[greta]

Hello everyone, 

I have a problem I cannot solve in power query: 
I have a defined list of versions, from 0 to 5, and a list of project. Each projct may or may not have a version. If the version is missing, it should be identified as the previous version (if the previous is missing with the previous and so on); so I have to build a column that contains said information.
As an example:

I can build a column used as version index, from 1 to 5, for each project:

But I cannot build the column "VerDef", that is the result I'd like to achieve. Could somebody help me out?
Many thanks in advance!

[Chewdata · Accepted solution]

Hey!,

Maybe not the shortest or most elegant of solutions, but this should do the trick. 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wys7OVtJRMlCK1YGxDZHYRkhsYyS2CZhdWVkJVw9hQ9RUVVXBzYSwTZViYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Progetto = _t, Ver = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Progetto", type text}, {"Ver", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Ver"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Columns"),
    add_IndexList = Table.AddColumn(#"Removed Duplicates", "IndexList", each {0,1,2,3,4,5}),
    #"Expanded List" = Table.ExpandListColumn(add_IndexList, "IndexList"),
    #"Merged Queries" = Table.NestedJoin(#"Expanded List", {"Progetto", "IndexList"}, #"Changed Type", {"Progetto", "Ver"}, "Expanded List", JoinKind.LeftOuter),
    #"Expanded Expanded List" = Table.ExpandTableColumn(#"Merged Queries", "Expanded List", {"Ver"}, {"Ver"}),
    #"Grouped Rows" = Table.Group(#"Expanded Expanded List", {"Progetto"}, {{"Table", each _, type table [Progetto=nullable text, List=number, Ver=nullable number]}}),
    add_VersionList = Table.AddColumn(#"Grouped Rows", "VersionList", each [Table][Ver]),
    #"Expanded Table" = Table.ExpandTableColumn(add_VersionList, "Table", {"IndexList", "Ver"}, {"Index", "Ver"}),
    fnGetMax = (vList as list, vIndex as number) =>

let
    Source = List.Select(vList, each _ <= vIndex),
    Output = List.Max(Source) ?? 0
in
    Output,
    add_DefVer = Table.AddColumn(#"Expanded Table", "DefVer", each if [Ver] = null then if [Index] = 0 then List.Min([VersionList]) else fnGetMax([VersionList], [Index]) else [Ver]),
    #"Removed Columns1" = Table.RemoveColumns(add_DefVer,{"VersionList"})
in
    #"Removed Columns1"

In this solution I create a list that contains all the available versions. The function gives back the largest item on the list that is lower than the index. For missing 0 versions it gets the MIN from that list.

[jgeddes · Accepted solution]

Here is a pbix file that transforms...

Into...

Hope this helps get you pointed in the right direction.

[greta]

It is perfect, thank you!

[Chewdata · Accepted solution]

Hey!,

Maybe not the shortest or most elegant of solutions, but this should do the trick. 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wys7OVtJRMlCK1YGxDZHYRkhsYyS2CZhdWVkJVw9hQ9RUVVXBzYSwTZViYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Progetto = _t, Ver = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Progetto", type text}, {"Ver", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Ver"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Columns"),
    add_IndexList = Table.AddColumn(#"Removed Duplicates", "IndexList", each {0,1,2,3,4,5}),
    #"Expanded List" = Table.ExpandListColumn(add_IndexList, "IndexList"),
    #"Merged Queries" = Table.NestedJoin(#"Expanded List", {"Progetto", "IndexList"}, #"Changed Type", {"Progetto", "Ver"}, "Expanded List", JoinKind.LeftOuter),
    #"Expanded Expanded List" = Table.ExpandTableColumn(#"Merged Queries", "Expanded List", {"Ver"}, {"Ver"}),
    #"Grouped Rows" = Table.Group(#"Expanded Expanded List", {"Progetto"}, {{"Table", each _, type table [Progetto=nullable text, List=number, Ver=nullable number]}}),
    add_VersionList = Table.AddColumn(#"Grouped Rows", "VersionList", each [Table][Ver]),
    #"Expanded Table" = Table.ExpandTableColumn(add_VersionList, "Table", {"IndexList", "Ver"}, {"Index", "Ver"}),
    fnGetMax = (vList as list, vIndex as number) =>

let
    Source = List.Select(vList, each _ <= vIndex),
    Output = List.Max(Source) ?? 0
in
    Output,
    add_DefVer = Table.AddColumn(#"Expanded Table", "DefVer", each if [Ver] = null then if [Index] = 0 then List.Min([VersionList]) else fnGetMax([VersionList], [Index]) else [Ver]),
    #"Removed Columns1" = Table.RemoveColumns(add_DefVer,{"VersionList"})
in
    #"Removed Columns1"

In this solution I create a list that contains all the available versions. The function gives back the largest item on the list that is lower than the index. For missing 0 versions it gets the MIN from that list.

[greta]

It works, many thanks!