Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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
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"
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"
It did work! thanks a lot, could you give me some insight in what the code is doing
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
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], ",")
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.