Forum Discussion
Pivot column error
Can you share your code from the Advanced Editor and indicate where the error occurs?
Otherwise it's not clear to me why you would want to repivot?
If this is a follow up on your previous post, it was my understanding that you should unpivot in Power Query and proceed with creating measures in DAX.
THanks MarcelBeug
First I unpivot the column with the month.
Then I pivot again ...
My Query is like this.
And for some reason it just dragged on like forever
let
Source = Excel.Workbook(File.Contents("C:\Users\Vuong\Documents\Outlet Data\Revised data outlet 2016\DNU.xlsx"), null, true),
Customer_Sheet = Source{[Item="Customer",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Customer_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Location", type text}, {"Group", type text}, {"Ter", type text}, {"CustomerCode", Int64.Type}, {"CustomerName", type text}, {"OutletCode", type text}, {"OutletName", type text}, {"SalesRouteCode", type text}, {"SalesRouteName", type text}, {"SalesRouteType", type text}, {"OutletLevel1", Int64.Type}, {"OutletLevel1Name", type text}, {"OutletLevel2", type text}, {"OutletLevel2Name", type text}, {"Address1", type text}, {"Address2", type text}, {"Address3", type text}, {"AnchorAccount", type text}, {"Club", type text}, {"Visicooler", type text}, {"Ngay Mo Moi", type datetime}, {"Trang Thai KH", type text}, {"TerritoryL3", type text}, {"TerritoryL3Name", type text}, {"TerritoryL4", type text}, {"TerritoryL4Name", type text}, {"Quantity_201601", Int64.Type}, {"Quantity_201602", Int64.Type}, {"Quantity_201603", Int64.Type}, {"Quantity_201604", Int64.Type}, {"Quantity_201605", Int64.Type}, {"Quantity_201606", Int64.Type}, {"Quantity_201607", Int64.Type}, {"Quantity_201608", Int64.Type}, {"Quantity_201609", Int64.Type}, {"Quantity_201610", Int64.Type}, {"Quantity_201611", Int64.Type}, {"Quantity_201612", Int64.Type}, {"Quantity_201701", Int64.Type}, {"Quantity_201702", Int64.Type}, {"So Lan Mua Hang Trong Thang_201601", Int64.Type}, {"So Lan Mua Hang Trong Thang_201602", Int64.Type}, {"So Lan Mua Hang Trong Thang_201603", Int64.Type}, {"So Lan Mua Hang Trong Thang_201604", Int64.Type}, {"So Lan Mua Hang Trong Thang_201605", Int64.Type}, {"So Lan Mua Hang Trong Thang_201606", Int64.Type}, {"So Lan Mua Hang Trong Thang_201607", Int64.Type}, {"So Lan Mua Hang Trong Thang_201608", Int64.Type}, {"So Lan Mua Hang Trong Thang_201609", Int64.Type}, {"So Lan Mua Hang Trong Thang_201610", Int64.Type}, {"So Lan Mua Hang Trong Thang_201611", Int64.Type}, {"So Lan Mua Hang Trong Thang_201612", Int64.Type}, {"So Lan Mua Hang Trong Thang_201701", Int64.Type}, {"So Lan Mua Hang Trong Thang_201702", Int64.Type}, {"So Lan Vieng Tham Theo Ke Hoach_201601", Int64.Type}, {"So Lan Vieng Tham Theo Ke Hoach_201602", Int64.Type}, {"So Lan Vieng Tham Theo Ke Hoach_201603", Int64.Type}, {"So Lan Vieng Tham Theo Ke Hoach_201604", Int64.Type}, {"So Lan Vieng Tham Theo Ke Hoach_201605", Int64.Type}, {"So Lan Vieng Tham Theo Ke Hoach_201606", Int64.Type}, {"So Lan Vieng Tham Theo Ke Hoach_201607", Int64.Type}, {"So Lan Vieng Tham Theo Ke Hoach_201608", Int64.Type}, {"So Lan Vieng Tham Theo Ke Hoach_201609", Int64.Type}, {"So Lan Vieng Tham Theo Ke Hoach_201610", Int64.Type}, {"So Lan Vieng Tham Theo Ke Hoach_201611", Int64.Type}, {"So Lan Vieng Tham Theo Ke Hoach_201612", Int64.Type}, {"So Lan Vieng Tham Theo Ke Hoach_201701", Int64.Type}, {"So Lan Vieng Tham Theo Ke Hoach_201702", Int64.Type}, {"So Lan Vieng Tham Thuc Te_201601", Int64.Type}, {"So Lan Vieng Tham Thuc Te_201602", Int64.Type}, {"So Lan Vieng Tham Thuc Te_201603", Int64.Type}, {"So Lan Vieng Tham Thuc Te_201604", Int64.Type}, {"So Lan Vieng Tham Thuc Te_201605", Int64.Type}, {"So Lan Vieng Tham Thuc Te_201606", Int64.Type}, {"So Lan Vieng Tham Thuc Te_201607", Int64.Type}, {"So Lan Vieng Tham Thuc Te_201608", Int64.Type}, {"So Lan Vieng Tham Thuc Te_201609", Int64.Type}, {"So Lan Vieng Tham Thuc Te_201610", Int64.Type}, {"So Lan Vieng Tham Thuc Te_201611", Int64.Type}, {"So Lan Vieng Tham Thuc Te_201612", Int64.Type}, {"So Lan Vieng Tham Thuc Te_201701", Int64.Type}, {"So Lan Vieng Tham Thuc Te_201702", Int64.Type}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Location", "Group", "Ter", "CustomerCode", "CustomerName", "OutletCode", "OutletName", "SalesRouteCode", "SalesRouteName", "SalesRouteType", "OutletLevel1", "OutletLevel1Name", "OutletLevel2", "OutletLevel2Name", "Address1", "Address2", "Address3", "AnchorAccount", "Club", "Visicooler", "Ngay Mo Moi", "Trang Thai KH", "TerritoryL3", "TerritoryL3Name", "TerritoryL4", "TerritoryL4Name"}, "Attribute", "Value"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns","Attribute",Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv),{"Attribute.1", "Attribute.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", Int64.Type}}),
#"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[Attribute.1]), "Attribute.1", "Value")
in
#"Pivoted Column"
My suspicion is that the error already occurs at the "Changed Type" step.
You can check by adding a step after the "Changed Type" (in a copy of your file): choose "Keep Errors" from the drop down menu in the left upper corner of your table (see screenshot).
That should give you the rows with errors (i.e. data that can not be converted to the type of the column, e.g. fractional data that can not be converted to Int64.Type).
If this produces an empty table, than my suspicion seems incorrect (and I'm afraid I'm running out of suggestions...).