Forum Discussion
Pivoting Error
Here you go if you want to do it PQ. the trick is you have to tell PQ that every 4 rows should be treated as a single row after pivoting. so I have added index,divide and round up. use the code that i have pasted at last.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCsssKilNzPFNTM7IzEstVtJRcjQEEmXOAaEgjqFSrA5ORb6puflFlSBVeuam+BSCTUssS8zMSUzKSSVkLEh1cEFqUXJ+EVFqQRwjotxpRKo78RqL5k6g2lgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [resourcetype = _t, valuename = _t, capabilitiesname = _t, capabilityvalue = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"resourcetype", type text}, {"valuename", type text}, {"capabilitiesname", type text}, {"capabilityvalue", type number}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
#"Inserted Modulo" = Table.AddColumn(#"Added Index", "Modulo", each Number.Mod([Index], 4), type number),
#"Removed Columns" = Table.RemoveColumns(#"Inserted Modulo",{"Modulo"}),
#"Divided Column" = Table.TransformColumns(#"Removed Columns", {{"Index", each _ / 4, type number}}),
#"Rounded Up" = Table.TransformColumns(#"Divided Column",{{"Index", Number.RoundUp, Int64.Type}}),
#"Pivoted Column" = Table.Pivot(#"Rounded Up", List.Distinct(#"Rounded Up"[capabilitiesname]), "capabilitiesname", "capabilityvalue")
in
#"Pivoted Column"Thanks,
thingsclump
accpet solution if this works for you.
Why do you need them in columns? You can use the 'Matrix' visual to do the pivoting for you (don't usually recommend doing this in Power Query). This article is for Excel, but applies to Power BI data tables too: https://www.myonlinetraininghub.com/excel-tabular-data-format
In order to see more info on the error, click in the cell that says 'Error', but do NOT click on the link Error. You'll see a message at the bottom of the table explaining what the error is.
Hey.. Hi AllisonKennedy
I am aware about the Matrix Visual.. here i need to join this table with another by making a relationship. So when making the relationship and adding Matrix the Visual Par seemed not good and it was the confusing thing to understand when we see the visual part.
Below is my table and the when i join the VM Sizes Table with my below one, it must show the Features of VM that i mentioned in 1st post.
Now when adding the matrix it is looking like below and i have also tried several other tricks by alternating the rows and columns in the Visualization editor option, even though it not worked.
When i am adding the VM Sizes table fields here, i am not getting the required data.. it is either getting to be selected First or last only
Thanks
Sudharshan1919 Can you provide sample data and a picture of the end result you want please?
The SELECTEDVALUE function in DAX may help get you the info you need in the VALUES section of the matrix (rather than being stuck with 'first' and 'last').
Also, did you check what the error message said in Power Query? We can't help troubleshoot without knowing what the error is.
Hello AllisonKennedy ,
Got response from Thingsclump and he solved my requirement. Please refer to his reply in this thread.
Thanks
Sudharshan1919 Please accept his Thingsclump 's post as solution so that we all know this is solved and don't spend unnecessary time on it, but also so others with similar issue can find it easier.
Glad you got it solved. 😀