Usable query from a XML with just two columns for both data rows and column names

[danextian]

Hi,

I need help trasforming the table below. As you can see, the rows and column data is similar with how it looks when unpivoting a table. I thought pivot is just undoing unpivot but I was wrong. All I can think of now is creating multiple queries for each unique value in name  column, adding index and then merging them -- which is very manual. 

field	name
Åland Islands	Country or Area
2015	Year
Total	Area
Both Sexes	Sex
MARIEHAMN	City
City proper	City type
Estimate - de jure	Record Type
Final figure, complete	Reliability
2016	Source Year
Åland Islands	Country or Area
2015	Year
Total	Area
Male	Sex
MARIEHAMN	City
City proper	City type
Estimate - de jure	Record Type
Final figure, complete	Reliability
2016	Source Year
Åland Islands	Country or Area
2015	Year
Total	Area
Female	Sex
MARIEHAMN	City
City proper	City type
Estimate - de jure	Record Type
Final figure, complete	Reliability
2016	Source Year
Åland Islands	Country or Area
2014	Year
Total	Area
Both Sexes	Sex
MARIEHAMN	City
City proper	City type
Estimate - de jure	Record Type
Final figure, complete	Reliability

[Interkoubess]

Hi @danextian,

I don,t know if I figured out what you are requesting.

I exported your data and created a Table ( report) as you can see on the image below:

Also in a excel file, you can create a pivot table with your 2 columns in Rows( repeated items).

Let us know if it is not what you want...

[danextian]

hi @Interkoubess

The result I'm trying to achieve is something like this:

Country or Area	Year	Area	Sex	City	City type	Record Type	Reliability
Åland Islands	2015	Total	Both Sexes	MARIEHAMN	City proper	Estimate - de jure	Final figure, complete
Åland Islands	2015	Total	Male	MARIEHAMN	City proper	Estimate - de jure	Final figure, complete
Åland Islands	2015	Total	Female	MARIEHAMN	City proper	Estimate - de jure	Final figure, complete

[MarcelBeug · Accepted solution]

You first need to add an Index starting at 0 and then Integer-Divide this by 9, so you get 9x 0, 9x 1 etcetera, i.e. each set of 9 fields gets the same value.

Now you can pivot with advanced option "Don't Aggregate".

All steps are created using standard menu options.

let
    Source = Excel.Workbook(File.Contents("C:\Users\Marcel\Documents\Forum bijdragen\Power BI Community\Usable query from a XML with just two columns for both data rows and column names.xlsx"), null, true),
    Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"field", type any}, {"name", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
    #"Integer-Divided Column" = Table.TransformColumns(#"Added Index", {{"Index", each Number.IntegerDivide(_, 9), Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Integer-Divided Column", List.Distinct(#"Integer-Divided Column"[name]), "name", "field")
in
    #"Pivoted Column"

Edit: now you can remove the Index column (I forgot). 

[danextian]

Thanks @MarcelBeug,

That works. I didnt know I could do that with index and pivot.