Replace Values Based Vased on Condition(s)

[Anonymous]

Hi Folks,

I have an issue that I can't seem to quite figure out. I have a table that has a project column with 3 separate numerical condition columns.

Original:

Expected:

I have the current form of the table as well as how I need the table to be formatted. Also, I can't remove any rows as otherwise that'll mess up the results. Any help would be appreciated.

Here's a link to the sample file:
https://drive.google.com/file/d/12VxDFGkf_vcO5LwoAmHla4jl-pDcU8xd/view?usp=sharing 

Thank you. 

[Anonymous]

Thank you very much! This absolutely does the trick!

[lbendlin · Accepted solution]

That seems like a very inefficient format. Might want to consider unpivoting instead.

Anyway, here goes:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKz0pNLjFU0lEyBWJjIDZRitWhQMIILADBxpRJGCPZYUqZhAlUwoRyCYSvKZYwAwrklebkoFFUVmFOFxUWdFFhCU8naEFJuoShAVDECB7LlMoYwiMYLRWTJWNEMCQoURILAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, #"Condition 1" = _t, #"Condition 2" = _t, #"Condition 3" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Condition 1", Int64.Type}, {"Condition 2", Int64.Type}, {"Condition 3", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Replaced Value1" = Table.ReplaceValue(#"Added Index",each [Condition 1], each if Number.Mod([Index],3) = 0 then [Condition 1] else null ,Replacer.ReplaceValue,{"Condition 1"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1",each [Condition 2], each if Number.Mod([Index],3) = 1 then [Condition 2] else null ,Replacer.ReplaceValue,{"Condition 2"}),
    #"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2",each [Condition 3], each if Number.Mod([Index],3) = 2 then [Condition 3] else null ,Replacer.ReplaceValue,{"Condition 3"})
in
    #"Replaced Value3"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".