Forum Discussion
How to gathering data into column?
Hi guys, I'm quite new to Power BI so I need some help from you.
I have collected some data about computer specification. But the data isn't organized as I have shown on this page.
| Serial | 3500013650 |
| OS | Window10 |
| CPU | Intel Core i5 |
| Clock | 2.4GHz |
| RAM | 4GB |
| Serial | 350001361 |
| OS | Window10 |
| CPU | Intel Core i5 |
| Clock | 2.4GHz |
| RAM | 4GB |
| Serial | 350001362 |
| OS | Window10 Pro |
| CPU | Intel Core i7 |
| Clock | 2.4GHz |
| RAM | 8GB |
My requirement is I want to organize them by moving some data that is the same type into their column as an example.
| Serial | OS | CPU | Clock | RAM |
| 3500013650 | Window10 | Intel Core i5 | 2.4GHz | 4GB |
| 350001361 | Window10 | Intel Core i5 | 2.4GHz | 4GB |
| 350001362 | Window10 Pro | Intel Core i7 | 2.4GHz | 8GB |
Please help me to solve this so I'm not sure this can be done in the Query Editor in Power BI or not? Or I have to do in other software, and what's name of the Program?
Thank you so much.
Hi saranp780 ,
At first, you need to import it and set two column names. Open query editor.
Then add an index column.
Pivot column Type. Do not aggregate value.
Then you can get the following table.
Select column OS -> hold down shift -> left-click on column RAM. Go to the tab Transform -> select Fill -> press Up.
Filter out null values from column Serial. Remove index column. Click Close & Apply.Here is the all codes in the Advanced Editor.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCk4tykzMUdJRMjY1MDAwNDYzNVCK1YlW8g8GioVn5qXklxtCRJwDQoFCnnklqTkKzvlFqQqZphDxnPzkbKCMkZ6Ju0cVWCjI0RcoYOLuBOZhWGFIcxuMMG1QCCjKx2GLOX5bLEC2xAIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}}), #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"(blank)", "Type"}, {"(blank).1", "value"}}), #"Added Index" = Table.AddIndexColumn(#"Renamed Columns", "Index", 0, 1), #"Pivoted Column" = Table.Pivot(#"Added Index", List.Distinct(#"Added Index"[Type]), "Type", "value"), #"Filled Up" = Table.FillUp(#"Pivoted Column",{"OS", "CPU", "Clock", "RAM"}), #"Filtered Rows" = Table.SelectRows(#"Filled Up", each ([Serial] <> null)), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Index"}) in #"Removed Columns"Best Regards,
Eads
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 Reply
Hi saranp780 ,
At first, you need to import it and set two column names. Open query editor.
Then add an index column.
Pivot column Type. Do not aggregate value.
Then you can get the following table.
Select column OS -> hold down shift -> left-click on column RAM. Go to the tab Transform -> select Fill -> press Up.
Filter out null values from column Serial. Remove index column. Click Close & Apply.Here is the all codes in the Advanced Editor.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCk4tykzMUdJRMjY1MDAwNDYzNVCK1YlW8g8GioVn5qXklxtCRJwDQoFCnnklqTkKzvlFqQqZphDxnPzkbKCMkZ6Ju0cVWCjI0RcoYOLuBOZhWGFIcxuMMG1QCCjKx2GLOX5bLEC2xAIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}}), #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"(blank)", "Type"}, {"(blank).1", "value"}}), #"Added Index" = Table.AddIndexColumn(#"Renamed Columns", "Index", 0, 1), #"Pivoted Column" = Table.Pivot(#"Added Index", List.Distinct(#"Added Index"[Type]), "Type", "value"), #"Filled Up" = Table.FillUp(#"Pivoted Column",{"OS", "CPU", "Clock", "RAM"}), #"Filtered Rows" = Table.SelectRows(#"Filled Up", each ([Serial] <> null)), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Index"}) in #"Removed Columns"Best Regards,
Eads
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.