Forum Discussion
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?
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.
7 Replies
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.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
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.
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], ",")
