Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Anonymous
Not applicable

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

View solution in original post

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.

 

View solution in original post

7 REPLIES 7
Anonymous
Not applicable

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

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
Not applicable

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

Anonymous
Not applicable

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

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
Super User
Super User

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

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Greg_Deckler
Super User
Super User

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors