How to gathering data into column?

[saranp780]

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.

[v-eachen-msft · Accepted solution]

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.