topic Re: API Data Transforming in Developer

API Data Transforming

Anonymous — Thu, 25 Aug 2022 18:44:00 GMT

I have an issue when transforming the data from an API and getting it into a useable format. I need to be calling a dynamic amount of columns from the API (Right now I have in the example below only 6 attributes pulling in, but the user can enter as many attributes as they want). Once they enter the attributes though, I have not been able to figure out how to format the data in a way that the steps in power query will be able to do it automatically for an ever-changing number of attributes.

Below is the table we have, an identifier, an attribute and its value. This is how the data is coming in.

1	treetype	elm
1	treename	elmhurst
1	treecolor	brown
1	location	SF
1	origin	WI
1	strength	3
2	treetype	elm
2	treename	elmhurst
2	treecolor	brown
2	location	SF
2	origin	WI
2	strength	3
3	treetype	elm
3	treename	elmhurst
3	treecolor	brown
3	location	SF
3	strength	3

What I need it to be is like the below, but I cannot figure out how to transform this for a number of attributes that are changing (as you see from the example above, tree 3 does not have an origin, so some data might also be incomplete).

	treetype	treename	treecolor	location	origin	strength
1	elm	elmhurst	brown	SF	WI	3
2	oak	oaky	green	LA	SF	2
3	evergreen	green	forest	WI		5

Any help would be appreciated!

Re: API Data Transforming

Daryl-Lynch-Bzy — Thu, 25 Aug 2022 20:49:58 GMT

HI @Anonymous - you should be able to use the Pivot function. The key part is "Don't Aggregate".

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fc7BCoMwDMbxd+nZy5J3EHbewYN4UClaqI3EiOztF0U6xqK3j3+g/dW1e7jCCXsv79nr9HFyTfHNqZ3OPK68yM+tp0isu2PaUr5E6lsJlHS+ylyJwxD2Vj1zW/SRNMioE48ItgVuLHBpAdMChgUsC9oWvLHgpQVNC/7923wA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Index = _t, Attribute = _t, Value = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"Attribute", type text}, {"Value", type text}}), #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Attribute]), "Attribute", "Value") in #"Pivoted Column"

Re: API Data Transforming

Anonymous — Thu, 25 Aug 2022 21:18:25 GMT

This works- I was also able to add more attribute / value pairs and this also works when you add more in power query without touching the steps. Exactly what I was looking for. Thank you so much!