cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Leoaq Regular Visitor
Regular Visitor

Custom column with ifs

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? 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
jborro Member
Member

Re: Custom column with ifs

Hi @Leoaq 

 

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

View solution in original post

Super User
Super User

Re: Custom column with ifs

Hi @Leoaq 

 

Both @Greg_Deckler  and @jborro  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.

 

View solution in original post

7 REPLIES 7
Super User
Super User

Re: Custom column with ifs

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.


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Super User
Super User

Re: Custom column with ifs

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], ",")

 


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Highlighted
jborro Member
Member

Re: Custom column with ifs

Hi @Leoaq 

 

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

View solution in original post

Super User
Super User

Re: Custom column with ifs

Hi @Leoaq 

 

Both @Greg_Deckler  and @jborro  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.

 

View solution in original post

jborro Member
Member

Re: Custom column with ifs

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

Leoaq Regular Visitor
Regular Visitor

Re: Custom column with ifs

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

Super User
Super User

Re: Custom column with ifs

Hi @jborro 

 

Thanks!

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

 

Many Thanks

Mariusz

 

 

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (4,310)