How do you move data from cells into rows to cells in the same column

[ojje]

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

[drewlewis15 · Accepted 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.

[drewlewis15 · Accepted 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.

[ojje]

Thanks. Half-way 🙂 

It's always new values so I have to filter on MAX Id. Have tested, but does not figure it out.

[drewlewis15 · Accepted solution]

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"

[ojje]

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?

[drewlewis15]

Do you have a gateway installed?  See link below.

Power BI Gateway

[ojje]

Not on my computer. I need a server based one... I understand the problem. Will do at test with a local gateway.

[ojje]

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...

https://app.powerbi.com/view?r=eyJrIjoiZjVjN2FhNjctMDgyMS00ZjM2LWIxNTYtNDY5NDU3OTA2NzJhIiwidCI6IjY1Z...

[ojje]

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... 

https://app.powerbi.com/view?r=eyJrIjoiZjVjN2FhNjctMDgyMS00ZjM2LWIxNTYtNDY5NDU3OTA2NzJhIiwidCI6IjY1Z...