cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ojje Frequent Visitor
Frequent Visitor

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

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:

IdQAQBQCAnswerFrom
1231222Carl
212319Max
35713Steve
41169Sue
51846Lisa
69114Andrew

 

Needs for Radar chart:

QuestionAnswerAnswerName
QA9Andrew
QB11Andrew
QC4Andrew

RadarIssue.PNG

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
drewlewis15 Member
Member

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

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

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

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"

8 REPLIES 8
ojje Frequent Visitor
Frequent Visitor

How to change data from columns to rows

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

Highlighted
ojje Frequent Visitor
Frequent Visitor

How to move data from columns to rows

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

drewlewis15 Member
Member

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

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 Frequent Visitor
Frequent Visitor

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

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

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

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 Frequent Visitor
Frequent Visitor

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

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

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

Do you have a gateway installed?  See link below.

 

Power BI Gateway

ojje Frequent Visitor
Frequent Visitor

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

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