Pivot column error

[minhvuong93]

Dear all,

I did a Unpivot of multiple columns and then try to re-pivot this column.

For some reasons I dont know, PBI keeps trying to convert some data into number that I did not want to convert, and the Pivoting Columns always failed.

Can anyone help me with this?

"DataFormat.Error: We couldn't convert to Number."

[MarcelBeug]

Based on your very limited information, my best guess would be that you need to choose "Don't aggregate".

[minhvuong93]

Hi @MarcelBeug,

Thank you for your attention. Actually I did select the "don't aggreagate"

But is it because the data set is too big? the original data is around 30MB but after I re-pivot the column, it always take a very longtime and froze at 50MB sth.

Basically, my data is something like this...

One Customer can by from any Sales Rep and they have their own Sale , Frequency of purchase and Call (made by Sale Rep).

The period goes from Jan -2016 to Mar-2017

[MarcelBeug]

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.

[minhvuong93]

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"

[MarcelBeug]

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...).