Within a Matrix, only showing values or a column at the lowest level

[Jddalberth]

Hi Everyone,

I am currently creating a matrix using a multi-select parameter to allow an end-user to create a customized Hierarchy for their own analysis. The parameter works great, however, I have a data field that needs to only be shown at the lowest level (whether that it is just blank or the column disappears does not matter). As you can see in the picture below, "Test 6" is always the lowest field and represents the field where I need the "IRR" field to populate once fully expanded to. Does anyone have a solution to this? I have seen a few for static matrices but the parameter makes things tougher.

[v-linyulu-msft · Accepted solution]

Thanks for the reply from lbendlin , please allow me to provide another insight:

Hi, @Jddalberth 

Regarding the issue you raised, my solution is as follows:

1.First I have created the following table and the column names and data are the data you have given:

2.Then I used the following M language in PowerQuery: 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTICYmMgNgFiU6VYnWiwCIhnBsQWQGxoABY2BivQUTIHYkuQsKFSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Test 1" = _t, #"Test 2" = _t, #"Test 3" = _t, #"Test 4" = _t, #"Test 5" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Test 1", Int64.Type}, {"Test 2", Int64.Type}, {"Test 3", Int64.Type}, {"Test 4", Int64.Type}, {"Test 5", Int64.Type}}),
    #"Demoted Headers" = Table.DemoteHeaders(#"Changed Type"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Demoted Headers",{{"Column1", type any}, {"Column2", type any}, {"Column3", type any}, {"Column4", type any}}),
    #"Transposed Table" = Table.Transpose(#"Changed Type1"),
    #"Added Custom" = Table.AddColumn(#"Transposed Table", "Custom", each List.Max([Column1])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Custom"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns", "Column1", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Column1.1", "Column1.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type2", {"Column1.1"}, {{"Max", each List.Max([Column1.2]), type nullable number}}),
    #"Merged Queries" = Table.NestedJoin(#"Grouped Rows", {"Column1.1", "Max"}, #"Changed Type2", {"Column1.1", "Column1.2"}, "Changed Type2", JoinKind.LeftOuter),
    #"Expanded Changed Type2" = Table.ExpandTableColumn(#"Merged Queries", "Changed Type2", {"Column2", "Column3", "Column4"}, {"Changed Type2.Column2", "Changed Type2.Column3", "Changed Type2.Column4"}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Expanded Changed Type2", {{"Max", type text}}, "en-US"),{"Column1.1", "Max"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
    #"Transposed Table1" = Table.Transpose(#"Merged Columns"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true])
in
    #"Promoted Headers"

3.Here's my final result, which I hope meets your requirements.

Please find the attached pbix relevant to the case.

 
Of course, if you have any new ideas, you are welcome to contact us.
 

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

What is that last screenshot representing?  Looks more like a data view?