Forum Discussion
Unpivot only selected columns not working
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".
- KH11NDR7 years agoHelper IV
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.
- KH11NDR7 years agoHelper IV
Reloaded the data and worked fine.................................Friday gremlings.
- v-chuncz-msft7 years agoCommunity Support
Glad to hear that. You may help accept the solution above. Your contribution is highly appreciated.