Custom column with ifs

[Anonymous]

I have another trouble with power query, the trouble is this i have 8 columns that display "OK", "REFRESH" or  "TEMPLATE" each, depending on an analysis they make, but i have to add another column that tells which of those 8 columns are in  "REFRESH" or  "TEMPLATE"

for example

PUESTO/ ID / JI / CT/ UP / TAM/ REGION/ LOC/

OK/REFRESH/TEMPLATE/OK/OK/TEMPLATE/OK/OK/

i need it to print "ID JI TAM" because those columns are the ones that have a value different than ok 

i am coding this with lots of IF statements and i have already 100+ lines of code, is there a more efficent way to do it? 

[Anonymous · Accepted solution]

Hi @Anonymous 

In PQ you can do something like this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8vdW0lEKcnULcg32ALJCXH0DfBxDXIFMsAyYwCIYqxONXRdCEF0rmgyMGxsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [PUESTO = _t, ID = _t, JI = _t, CT = _t, UP = _t, TAM = _t, REGION = _t, LOC = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"PUESTO", type text}, {"ID", type text}, {"JI", type text}, {"CT", type text}, {"UP", type text}, {"TAM", type text}, {"REGION", type text}, {"LOC", type text}}),
    
    Headers = List.Buffer(Table.ColumnNames(#"Changed Type")),
    fCombine = (L as list, Filter as text) => Text.Combine(List.Zip(List.Select(L, (n) => n{1} = Filter)){0}, ","),
    fFilter = (r)=> 
        let 
            List = List.Buffer(List.Zip({Headers} & {Record.ToList(r)})), 
            Ok = fCombine(List, "OK"), 
            Template = fCombine(List, "TEMPLATE"),
            Refresh = fCombine(List, "REFRESH"),
            Output = Table.FromColumns({{Ok}, {Template}, {Refresh}}, {"Ok", "Template", "Refresh"}) 
        in Output,
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each fFilter(_)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Ok", "Template", "Refresh"}, {"Ok", "Template", "Refresh"})
in
    #"Expanded Custom"

Kind regards,

JB

[Anonymous · Accepted solution]

Hi @Anonymous 

In PQ you can do something like this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8vdW0lEKcnULcg32ALJCXH0DfBxDXIFMsAyYwCIYqxONXRdCEF0rmgyMGxsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [PUESTO = _t, ID = _t, JI = _t, CT = _t, UP = _t, TAM = _t, REGION = _t, LOC = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"PUESTO", type text}, {"ID", type text}, {"JI", type text}, {"CT", type text}, {"UP", type text}, {"TAM", type text}, {"REGION", type text}, {"LOC", type text}}),
    
    Headers = List.Buffer(Table.ColumnNames(#"Changed Type")),
    fCombine = (L as list, Filter as text) => Text.Combine(List.Zip(List.Select(L, (n) => n{1} = Filter)){0}, ","),
    fFilter = (r)=> 
        let 
            List = List.Buffer(List.Zip({Headers} & {Record.ToList(r)})), 
            Ok = fCombine(List, "OK"), 
            Template = fCombine(List, "TEMPLATE"),
            Refresh = fCombine(List, "REFRESH"),
            Output = Table.FromColumns({{Ok}, {Template}, {Refresh}}, {"Ok", "Template", "Refresh"}) 
        in Output,
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each fFilter(_)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Ok", "Template", "Refresh"}, {"Ok", "Template", "Refresh"})
in
    #"Expanded Custom"

Kind regards,

JB

[Mariusz · Accepted solution]

Hi @Anonymous 

Both @Greg_Deckler  and @Anonymous  are very good working solutions, not trying to say my one will be any better but it is an alternative, please see the below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8vdW0lEKcnULcg32ALJCXH0DfBxDXIFMsAyYwCIYqxONXRdCEF0rmgyMGxsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [PUESTO = _t, ID = _t, JI = _t, CT = _t, UP = _t, TAM = _t, REGION = _t, LOC = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"PUESTO", type text}, {"ID", type text}, {"JI", type text}, {"CT", type text}, {"UP", type text}, {"TAM", type text}, {"REGION", type text}, {"LOC", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each 
    let 
        recordToTable = Record.ToTable( _ ),
        groupedRows = Table.Group( recordToTable, {"Value"}, {{"Columns", each _[Name], type list}}),
        extractedValues = Table.TransformColumns( groupedRows, {"Columns", each Text.Combine( List.Transform(_, Text.From), ", "), type text}),
        pivotedColumn = Table.Pivot( extractedValues, List.Distinct( extractedValues[Value] ), "Value", "Columns")
    in 
        pivotedColumn, type table ),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"OK", "REFRESH", "TEMPLATE"}, {"OK", "REFRESH", "TEMPLATE"})
in
    #"Expanded Custom"

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

[Anonymous]

It did work! thanks a lot, could you give me some insight in what the code is doing

[Anonymous]

Hi @Mariusz 

A very elegant solution. I think in given circumstances, it is both easier to read and quicker to run than my version. I will definitely add it to my code vault.😁

Kind regards,

JB

[Mariusz]

Hi @Anonymous 

Thanks!

I will be following you closely as I've seen some really good stuff from you as well.

Many Thanks

Mariusz

[Greg_Deckler]

Second, here is a DAX way of doing it. Note, I am essentially unpivoting the columns in DAX:

Column = 
VAR __puesto = { ("Puesto", [PUESTO]) }
VAR __id = { ("ID", [ID]) }
VAR __ji = { ("JI", [JI]) }
VAR __ct = { ("CT", [CT]) }
VAR __up = { ("UP", [UP]) }
VAR __tam = { ("TAM", [TAM]) }
VAR __region = { ("REGION", [REGION]) }
VAR __loc = { ("LOC", [LOC]) }
VAR __table = UNION(__puesto, __id, __ji, __ct, __up, __tam, __region, __loc)
VAR __table1 = FILTER(__table, [Value2] <> "OK")
RETURN
CONCATENATEX(__table1, [Value1], ",")

[Greg_Deckler]

First, do you have some sort of "id" column that is unique per row? If so, you could unpivot your columns and probably make this a whole lot easier.