API - Columns became rows

[Anonymous]

Edit: Solved, I was going about this the wrong way. Transpose columns before expanding rows and it works. Whoops 

First time trying to use an API directly into Power BI and ran into some issues, or maybe I'm just doing it wrong?

I'm getting data from Statistics Sweden (Swedish government agency) through this link.  Which I add as a "web" data source in Power BI.

What I want is a column for each of the categories with rows of values, like this:

region	type	tablecontent	2020K1	2020K2	2020K3	2020K4
x	z	a1	1741	1721	659	1232
x	z	a2	961	968	873	1004
x	y	b1	116	57	54	69
x	y	b2	94	117	46	118

But instead I get this:

code	text	valuetexts	elimination
Region	region	[List]	TRUE
Hustyp	hustyp	[List]	TRUE
ContentsCode	tabellinnehåll	[List]	null
Tid	kvartal	[List]	null

And if I expand the [List] to new rows I just get a long list with the data in a single column like mentioned. 

Am I missing something in the previous "Applied Steps" in my query settings?

Thanks 

[v-lionel-msft]

Hi @Anonymous ,

Try to connect to this data source and expanded the List. Then, transform the table.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

Thanks, I was doing it wrong...

If i Transpose the columns before expanding the lists it works.