Forum Discussion
Helper IIPivot column error
Community ChampionBased on your very limited information, my best guess would be that you need to choose "Don't aggregate".
minhvuong93Hi 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"