Forum Discussion
Pivoting Error
Hey Hi Guys,
I am new to Power BI and facing some basic issues. Appreciate if anyone could help.
I have one issue at Pivoting the elements. I am calling this API ("https://management.azure.com/subscriptions/{Subscription}/providers/Microsoft.Compute/skus?api-version=2021-07-01") and getting the data for listing out all the sizes of VMs in Azure and in this for one column i have a selected on some particular fields to be in my data. Now those are saved as rows and i need them as separate columns, I have tried Pivoting that column.. but the required data is getting "Error"
I am getting the data as below
In the Column "value.capabilitiesname" i have selected only the below fields as per my requirement, the rest of them are hidden. After selecting the required fields i am getting the required data, but the other columns are getting duplicated. That means i have selected vCPU, MemoryGB, vCPUsAvailable, vCPUsPerCore and for every filed i am getting the data duplicacy of other columns. They are getting repeated for each of the filed i have selected.
So to get rid of those i have Pivoted the columns as below and getting the error if i select Don't Aggregate option while Pivoting.
As you can see below, i got the required format but the other data fields are in Error.
Kindly help me and mention the step by step process to sort out from this issue.
Thanks,
Sudharshan.
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.
6 Replies
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.
