Pivot tables help

[Anonymous]

Greetings Community. I'm having problems with pivoting tables.

I have a talbe like this

City name as headers, the following row has the name of the country that city belongs to. I wish to make a table where I can have a column for date, a column for city, a column for country and a column for the values (followers). Like this:

I'd really appreciate your help on this please.

Thanks in advance

[v-piga-msft]

Hi @Anonymous,

You may could use Unpivot column in Query Editor.

If you still need help, please share some data sample.

Best  Regards,

Cherry

[Anonymous]

Hello @v-piga-msft, thanks for the quick response.

This is a piece my excel fille

I wish to make it look like this on Power BI:

I have tried to unpivot that column, but when I do that I get this:

So I lose the number value.

Thanks in advance.

[v-piga-msft · Accepted solution]

Hi @Anonymous,

For you scenario, I have create a table similar with your farmat.

Assuming that you have a table like below.

Fecha	A1	A2
	country1	country2
01/01/2018	10
01/02/2018	20
01/03/2018		10
01/04/2018	25
01/05/2018	25	30

1. Get data and go to Query edit.

2. Duplicate the table and unpviot the columns.

More details you could have a reference of the Applied steps in Query Editor for the attached pbix.

Here is the result.

Best Regards,

Cherry

[Anonymous]

Hello @v-piga-msft, thanks for the help.

That's exactly the table I would like to get! But for some reason I cannot.

This is my data in query editor, prior to unpivoting columns. Just to clarify, the first few months of 2016 are all null.

In query editor, the steps I'm following are:

1. Selecting all columns

2. Unpivot columns 

And this is what I get

I tried selecting every column except the Date column and this is what I get:

Sadly this is not looking as the table in your last post which is exactly how I'd like to get it.

Thanks again for all the help

Best regards

[v-piga-msft]

Hi @Anonymous,

This is my data in query editor, prior to unpivoting columns. Just to clarify, the first few months of 2016 are all null.

---

Please have a try with the steps in the Query Editer for the test pbix I attached last reply.

For you scenario, if your first few months of 2016 are all null, I'm afraid the related date rows will disappear after unpivot.

Best Regards,

Cherry

[Anonymous]

Hey @v-piga-msft, I really appreciate your help.

I tried using your pbix file but sadly it rpesents an error

DataSource.Error: Could not find a part of the path 'C:\Users\cherryg\Desktop\Book2.xlsx'.

So I can only see the table already with the format, Can't see the process of how you got it to look like that.

Thanks in advance

[v-piga-msft]

Hi @Anonymous,

You could copy my data sample which I pasted in the first reply to your excel and change the data source as yours in Advanced Editor in Query Editor.

Then you could refer to the steps.

Best  Regards,

Cherry