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 want to create a radar chart (fron Excel data to the left in the picture) and need to move the cells (in the row) with latest data (column Id with highest number) to a column so I can create a radar chart.
Please help me with the syntax. VLOOKUP?
Excel:
Id | QA | QB | QC | AnswerFrom |
1 | 23 | 12 | 22 | Carl |
2 | 12 | 3 | 19 | Max |
3 | 5 | 7 | 13 | Steve |
4 | 1 | 16 | 9 | Sue |
5 | 18 | 4 | 6 | Lisa |
6 | 9 | 11 | 4 | Andrew |
Needs for Radar chart:
Question | Answer | AnswerName |
QA | 9 | Andrew |
QB | 11 | Andrew |
QC | 4 | Andrew |
Solved! Go to Solution.
Select all "Q" columns and choose "Unpivot Columns" from the Transform tab within the Query Editor. From there, you can filter by ID to get the highest number.
Filter Id by 6 just to get the statement written in the advanced editor. Then replace "6" with "List.Max(#"Unpivoted Columns"[Id])
Note that the portion before [Id] is simply the name of your last step. In this case, Unpivoting the columns was my last step. You would need to replace #"Unpivoted Columns if you have a different step before the filtering step.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIyBhKGRiAWiHBOLMpRitWJVjKCCYPlLYGEb2IFWAYkYArE5iAJECe4JLUsFSxlAhICYTMgAdITXAqRAKk3tAASIBUgSZ/M4kSwDEyloSFU1jEvpSi1XCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Id = _t, QA = _t, QB = _t, QC = _t, AnswerFrom = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Id", Int64.Type}, {"QA", Int64.Type}, {"QB", Int64.Type}, {"QC", Int64.Type}, {"AnswerFrom", type text}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Id", "AnswerFrom"}, "Attribute", "Value"),
#"Filtered Rows" = Table.SelectRows(#"Unpivoted Columns", each ([Id] = List.Max(#"Unpivoted Columns"[Id])))
in
#"Filtered Rows"
Select all "Q" columns and choose "Unpivot Columns" from the Transform tab within the Query Editor. From there, you can filter by ID to get the highest number.
Thanks. Half-way 🙂
Filter Id by 6 just to get the statement written in the advanced editor. Then replace "6" with "List.Max(#"Unpivoted Columns"[Id])
Note that the portion before [Id] is simply the name of your last step. In this case, Unpivoting the columns was my last step. You would need to replace #"Unpivoted Columns if you have a different step before the filtering step.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIyBhKGRiAWiHBOLMpRitWJVjKCCYPlLYGEb2IFWAYkYArE5iAJECe4JLUsFSxlAhICYTMgAdITXAqRAKk3tAASIBUgSZ/M4kSwDEyloSFU1jEvpSi1XCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Id = _t, QA = _t, QB = _t, QC = _t, AnswerFrom = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Id", Int64.Type}, {"QA", Int64.Type}, {"QB", Int64.Type}, {"QC", Int64.Type}, {"AnswerFrom", type text}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Id", "AnswerFrom"}, "Attribute", "Value"),
#"Filtered Rows" = Table.SelectRows(#"Unpivoted Columns", each ([Id] = List.Max(#"Unpivoted Columns"[Id])))
in
#"Filtered Rows"
Thanks. My major problem is now that PBI is not updated when new data is written in the Excel file (from a PowerApps). I need to open the Excel file and then close it to have the PBI chart updated...
Do I need a database as source?
Not on my computer. I need a server based one... I understand the problem. Will do at test with a local gateway.
I want to create a Radar chart and need to find the latest data in the Excel file (the highest number of Id). Then I need to transform the data in the different columns to rows in a own column to be able to create a radar chart.
I have tried, but do not manage...
I want to create a Radar chart and need to find the latest data in the Excel file (the highest number of Id). Then I need to transform the data in the different columns to rows in a own column to be able to create a radar chart.
I have tried, but do not manage...
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.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |