Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
@KH11NDR Please post the sample data to replicate your issue and also helpful to resolve the issue quicker...
Proud to be a PBI Community Champion
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 |
@KH11NDR Thanks for that... What is your expected ouput, please...
Proud to be a PBI Community Champion
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".
@KH11NDR Not sure, why it's happening... But I am successfully unpivot the same without any issues..
Proud to be a PBI Community Champion
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.
Reloaded the data and worked fine.................................Friday gremlings.
Glad to hear that. You may help accept the solution above. Your contribution is highly appreciated.
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.
User | Count |
---|---|
93 | |
83 | |
77 | |
74 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |