The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hello all,
This is my second post here as I am new using Power BI.
I would need some help regarding a function that I try to use in a query. I have adapted a solution provided by @Smauro for another question (my first post).
Here my Start Table
I would like to obtain a "ResultTable" so that for each date and for each parameter A to E, if my value is lower / equal to 0.6, my result is true. Otherwise false.
For that I have tried to use this function :
GroupList1 = List.Transform(Columns1, each {_, Expression.Evaluate( "each List.MatchesAny(["& _ & "], each _ <= 0.8)", [List.MatchesAny = List.MatchesAny]), Logical.Type})
But my "ResultTable" is wrong. For exemple I should have "false" in my A column for the two dates.
Here my wrong "ResultTable"
Any Idea about what is wrong in my fonction? Another solution to obtain my "ResultTable" is also welcome.
Here the entire code :
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIyMDLQNTDSNQBxDKDYEI5jdaKVjNCVYWKQMmNsygxQaJAyhKXGCEsxTTNCV4aMIe5EtdQYp6WxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [No = _t, Date = _t, A = _t, B = _t, C = _t, D = _t, E = _t]),
PreviousStep = Table.TransformColumnTypes(Source,{{"No", Int64.Type}, {"Date", type date}, {"A", Int64.Type}, {"B", Int64.Type}, {"C", Int64.Type}, {"D", Int64.Type}, {"E", Int64.Type}}),
DateList = List.Transform(List.Distinct(PreviousStep[Date]), each {Text.From(_), Percentage.Type}),
Columns = List.Select(Table.ColumnNames(PreviousStep), each _ <> "Date" and _ <> "No"),
GroupList = List.Transform(Columns, each {_, Expression.Evaluate( "each List.Sum(["& _ & "]) / Table.RowCount(_)", [List.Sum = List.Sum, Table.RowCount = Table.RowCount]), Percentage.Type}),
#"Grouped Rows" = Table.Group(PreviousStep, {"Date"}, GroupList),
Columns1 = List.Select(Table.ColumnNames(#"Grouped Rows"), each _ <> "Date"),
GroupList1 = List.Transform(Columns1, each {_, Expression.Evaluate( "each List.MatchesAny(["& _ & "], each _ <= 0.6)", [List.MatchesAny = List.MatchesAny]), Logical.Type}),
#"Grouped Rows1" = Table.Group(PreviousStep, {"Date"}, GroupList1)
//#"Demoted Headers" = Table.DemoteHeaders(#"Grouped Rows"),
//#"Transposed Table" = Table.Transpose(#"Demoted Headers"),
//#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
//#"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{"Date", "Product"}}),
//#"Changed Types" = Table.TransformColumnTypes(#"Renamed Columns", List.Combine({{{"Product", type text}}, DateList}))
in
#"Grouped Rows1"
Solved! Go to Solution.
Hi @ItoDiaz ,
Try this code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIyMDLQNTDSNQBxDKDYEI5jdaKVjNCVYWKQMmNsygxQaJAyhKXGCEsxTTNCV4aMIe5EtdQYp6WxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [No = _t, Date = _t, A = _t, B = _t, C = _t, D = _t, E = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"No", Int64.Type}, {"Date", type date}, {"A", Int64.Type}, {"B", Int64.Type}, {"C", Int64.Type}, {"D", Int64.Type}, {"E", Int64.Type}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"No", "Date"}, "Attribute", "Value"),
#"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Custom", each if [Value] <= 0.6 then true else false),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Value"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Attribute]), "Attribute", "Custom"),
#"Changed Type1" = Table.TransformColumnTypes(#"Pivoted Column",{{"No", Int64.Type}, {"Date", type date}, {"A", type logical}, {"B", type logical}, {"C", type logical}, {"D", type logical}, {"E", type logical}})
in
#"Changed Type1"
Hi @ItoDiaz
Supposing you'd like to use a solution like then one you refer to, then:
1) Get a list of the Columns:
Columns = List.Select(Table.ColumnNames(PreviousStep), each _ <> "Date"),
2a) If your [Date] is distinct, then no need for grouping, you could just Transform the relevant columns:
TransformList = List.Transform(Columns, each {_, (curr) => curr <= 0.6, Logical.Type}),
Transform = Table.TransformColumns(PreviousStep,TransformList),
2b) If your [Date] is not distinct, then you can use:
GroupList = List.Transform(Columns, each {_, Expression.Evaluate( "each List.Min(["& _ & "]) <= 0.6", [List.Min = List.Min]), Logical.Type}),
Group = Table.Group(PreviousStep, {"Date"}, GroupList),
Note here that List.Min works like List.MatchesAny for numbers: If there is at least one value <=0.6 then the min will also be <=0.6
Cheers,
smauro
Hi @ItoDiaz
Supposing you'd like to use a solution like then one you refer to, then:
1) Get a list of the Columns:
Columns = List.Select(Table.ColumnNames(PreviousStep), each _ <> "Date"),
2a) If your [Date] is distinct, then no need for grouping, you could just Transform the relevant columns:
TransformList = List.Transform(Columns, each {_, (curr) => curr <= 0.6, Logical.Type}),
Transform = Table.TransformColumns(PreviousStep,TransformList),
2b) If your [Date] is not distinct, then you can use:
GroupList = List.Transform(Columns, each {_, Expression.Evaluate( "each List.Min(["& _ & "]) <= 0.6", [List.Min = List.Min]), Logical.Type}),
Group = Table.Group(PreviousStep, {"Date"}, GroupList),
Note here that List.Min works like List.MatchesAny for numbers: If there is at least one value <=0.6 then the min will also be <=0.6
Cheers,
smauro
try this:
Table.TransformColumns(yourtab,{{"A", each _>0.6},{"B", each _>0.6},{"C", each _>0.6},{"D", each _>0.6},{"E", each _>0.6}})
another way to get the result:
Table.FromRecords(Table.TransformRows(yourtab,each _&[A=[A]<0.6,B=[B]<0.6,C=[C]<0.6,D=[D]<0.6,E=[E]<0.6]))
or
Table.FromRecords(Table.TransformRows(yourtab,each [Date=[Date],A=[A]<0.6,B=[B]<0.6,C=[C]<0.6,D=[D]<0.6,E=[E]<0.6]))
@Anonymous
Thank you for your proposals. I have used this one for another query where I had fewer columns. In the case of this post I had 30 columns, I needed a faster solution. I should have to precise this detail.
to treat a generic list of columns
cols=Text.ToList("ABCDE"),
collist=List.Accumulate(cols,{},(s,c)=>s&{List.Transform(Table.Column(yourtab,c), each _ >0.6)})
in Table.FromColumns(collist, cols)
Hi @ItoDiaz ,
Try this code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIyMDLQNTDSNQBxDKDYEI5jdaKVjNCVYWKQMmNsygxQaJAyhKXGCEsxTTNCV4aMIe5EtdQYp6WxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [No = _t, Date = _t, A = _t, B = _t, C = _t, D = _t, E = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"No", Int64.Type}, {"Date", type date}, {"A", Int64.Type}, {"B", Int64.Type}, {"C", Int64.Type}, {"D", Int64.Type}, {"E", Int64.Type}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"No", "Date"}, "Attribute", "Value"),
#"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Custom", each if [Value] <= 0.6 then true else false),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Value"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Attribute]), "Attribute", "Custom"),
#"Changed Type1" = Table.TransformColumnTypes(#"Pivoted Column",{{"No", Int64.Type}, {"Date", type date}, {"A", type logical}, {"B", type logical}, {"C", type logical}, {"D", type logical}, {"E", type logical}})
in
#"Changed Type1"
Thanks a lot for your answer. I didn't use all your solution but I have tried and it works great.
Still, I learnt a lot regarding others issues ussing your solution and specially working with Table.UnpivotOtherColumns(#"Changed Type", {"No", "Date"}, "Attribute", "Value") for visualisations pourposes.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the August 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.