Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code FABINSIDER for a $400 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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.
Solved! Go to 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.
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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Check out the February 2025 Power BI update to learn about new features.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
84 | |
69 | |
68 | |
39 | |
37 |