Unpivot only selected columns not working

[KH11NDR]

Hi Guys,

I'm editing a query (table) which I've loded in from SQL and it allows me to Unpivot only selected columns and I'll rename the two columns as date and values.  But when i press close and apply, it doesn't apply the changes and says "The column 'first column' of the table wasn't found".

I have no problem in doing the same thing with data loaded in from excel.

Thanks guys.

[PattemManohar]

@KH11NDR Please post the sample data to replicate your issue and also helpful to resolve the issue quicker...

[KH11NDR]

@PattemManohar

Project ID	42917	42948	42979	43009	43040	43070	43101	43132	43160	43191	43221
1	0	0	0	0	0	9	63.5	63.5	53.5	70.5	76.5
2	28	39	51	57	50	43	46	34	5	0	0

[PattemManohar]

@KH11NDR Thanks for that... What is your expected ouput, please...

[KH11NDR]

This data is loaded via SQL 

let
Source = Sql.Databases("xxx"),

#"Pxxxxxx" = Source{[Name="xxxx"]}[Data],

#"dbo_'xxx$'" = #"xxxxxx"{[Schema="dbo",Item="'xxxxx$'"]}[Data],

#"Changed Type" = Table.TransformColumnTypes(#"dbo_'xxxx$'",{{"01/07/2017", type number}, {"01/08/2017", type number}, {"01/09/2017", type number},

{"01/10/2017", type number}, {"01/11/2017", type number}, {"01/12/2017", type number}, {"01/01/2018", type number}, {"01/02/2018", type number}, {"01/03/2018",

type number}, {"01/04/2018", type number}, {"01/05/2018", type number}, {"01/06/2018", type number}, {"01/07/2018", type number}, {"01/08/2018", type number},

{"01/09/2018", type number}, {"01/10/2018", type number}, {"01/11/2018", type number}, {"01/12/2018", type number}, {"01/01/2019", type number}, {"01/02/2019", type number},

{"01/03/2019", type number}, {"01/04/2019", type number}, {"01/05/2019", type number}, {"01/06/2019", type number}, {"01/07/2019", type number}, {"01/08/2019", type number},

{"01/09/2019", type number}, {"01/10/2019", type number}, {"01/11/2019", type number}, {"01/12/2019", type number}}),

#"Unpivoted Only Selected Columns" = Table.Unpivot(#"Changed Type", {"01/07/2017", "01/08/2017", "01/09/2017", "01/10/2017", "01/11/2017", "01/12/2017", "01/01/2018",

"01/02/2018", "01/03/2018", "01/04/2018", "01/05/2018", "01/06/2018", "01/07/2018", "01/08/2018", "01/09/2018", "01/10/2018", "01/11/2018", "01/12/2018", "01/01/2019", "01/02/2019",

"01/03/2019", "01/04/2019", "01/05/2019", "01/06/2019", "01/07/2019", "01/08/2019", "01/09/2019", "01/10/2019", "01/11/2019", "01/12/2019"}, "Attribute", "Value"),

#"Renamed Columns" = Table.RenameColumns(#"Unpivoted Only Selected Columns",{{"Attribute", "Dates"}, {"Value", " Values"}})
in
#"Renamed Columns"

Project ID	Date	Values
1	01/07/2017	0
1	01/08/2017	0
1	01/09/2017	0
1	01/10/2017	0
1	01/11/2017	0
1	01/12/2017	9
1	01/01/2018	63.5
1	01/02/2018	63.5
1	01/03/2018	53.5
1	01/04/2018	70.5
1	01/05/2018	76.5
2	01/07/2017	28
2	01/08/2017	39
2	01/09/2017	51
2	01/10/2017	57
2	01/11/2017	50
2	01/12/2017	43
2	01/01/2018	46
2	01/02/2018	34
2	01/03/2018	5
2	01/04/2018	0
2	01/05/2018	0

I know how to do it, but I'm getting the following message when I try to close and apply

"The column 'first column' of the table wasn't found".

[PattemManohar]

@KH11NDR Not sure, why it's happening...  But I am successfully unpivot the same without any issues..

[KH11NDR]

Are you using the data via SQL or Excel? can you share your Power BI file?

Thanks

I think mine might be a data type issue.

[KH11NDR]

Reloaded the data and worked fine.................................Friday gremlings.

[v-chuncz-msft]

@KH11NDR,

Glad to hear that. You may help accept the solution above. Your contribution is highly appreciated.

[KH11NDR]

@v-chuncz-msft

I haven't accepted a solution because there wasn't a solution given.  I understand @PattemManohar has been very helpful, but that wasn't the soloution as I already know how to pivot the table how I wanted it, My problem was the data wasn't loading with the pivoted columns, so it would be wrong for me to say that was the soloution when it wasn't and save people from trying it.  But for @PattemManohar great help, i have given  @PattemManohar a tick for thanks.

I hope you understand my reasons for not accepting a solution.

My solution was to reload the data from scratch and it worked.