Custom MQuery Column -populate a (conditioned) value for as long as another column has repeated rows

[Anonymous]

Hi,

I'm trying to break out and classify my data so I can make several slicers to slice a particular data column. However, the data for my slicers is all contained in 2 columns: a key column which classifies the type of data (ie, slicer header); and a value column which gives the corresponding value (the slicer dropdown options). The data is structured such that I have some result number I want to plot which has some number of entries in the key, value columns which provide context about the measurment. So the result entry repeats for as many key, value entries there are and then a new result will start repeating for the next run of data (1 number, many entries).

What I'd like to do is split out the key, value columns into separate columns depending on key type. However, when I use a conditional column, I get a bunch of null entries which means I can't slice on more than 1 value at a time. What I want is to have that column repeat the entry until the next run begins, each run can be classified by a run ID column. Right now my conditional column says something like:

if column = key and entry = name, then return corresponding value

I guess I want to condition it further to continue repeating if the Run ID is the same, until it's not and the next run starts. Is this possible? I've attached a picture for reference. Thanks!

[ChrisMendoza · Accepted solution]

@Anonymous -

This seems like a possible solution.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjLVMzCwUNJR8kvMTQVSXvkZeXmVQEZwSVFmXrohshAIxeog6XFMB2kxMUJRDhNBVeqempeSWgRk+CbmpKKrhwmDdVjqGRoYIRwUnJFYmgfXYIQkArcDpgPiHCNTFNUwEVSlcOe4peYiO8gI7iCoRGwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Some Result" = _t, Key = _t, Value = _t, Run = _t, Name = _t, Age = _t, Gender = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Some Result", type number}, {"Key", type text}, {"Value", type text}, {"Run", type text}, {"Name", type text}, {"Age", Int64.Type}, {"Gender", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","",null,Replacer.ReplaceValue,{"Name", "Gender"}),
    #"Filled Down" = Table.FillDown(#"Replaced Value",{"Name"}),
    #"Filled Up" = Table.FillUp(#"Filled Down",{"Gender"}),
    #"Grouped Rows" = Table.Group(#"Filled Up", {"Some Result"}, {{"Grouped", each _, type table [Some Result=number, Key=text, Value=text, Run=text, Name=text, Age=number, Gender=text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.Sort([Grouped],{{"Age",Order.Descending}})),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Some Result", "Key", "Value", "Run", "Name", "Age", "Gender"}, {"Some Result.1", "Key", "Value", "Run", "Name", "Age", "Gender"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Grouped", "Some Result.1"}),
    #"Filled Down1" = Table.FillDown(#"Removed Columns",{"Age"})
in
    #"Filled Down1"