none
Power Query Doesn't Load Complete CSV FIle After Pivot Transform RRS feed

  • Question

  • Hello, After transforming a CSV file using the following code I can load the entire file (45,000 rows) into the Excel Data Model.

    let
        Source = Csv.Document(File.Contents("C:\Users\jd\Desktop\2014 JW Annual Review\ALL STORES 01-07-15 AR OPEN.csv"),null,",",null,1252),
        #"First Row as Header" = Table.PromoteHeaders(Source),
        #"Changed Type" = Table.TransformColumnTypes(#"First Row as Header",{{"AR-OPEN", type text}, {"SALE CO", type text}, {"NAME", type text}, {"N-CD", Int64.Type}, {"REFER", type text}, {"JRNL CO", type text}, {"SCHED", type text}, {"AGME.CO.ID", Int64.Type}, {"SO", type text}, {"ODATE", type text}, {"JRNL TYPE", type text}, {"CONTROL", type text}, {"DATE", type text}, {"A-ACCT", type text}, {"CONTROL2", type text}}),
        #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"AR-OPEN"}),
        #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns","SALE CO",Splitter.SplitTextByDelimiter(" "),{"SALE CO.1", "SALE CO.2", "SALE CO.3", "SALE CO.4", "SALE CO.5", "SALE CO.6", "SALE CO.7", "SALE CO.8", "SALE CO.9", "SALE CO.10", "SALE CO.11", "SALE CO.12", "SALE CO.13", "SALE CO.14", "SALE CO.15", "SALE CO.16", "SALE CO.17", "SALE CO.18", "SALE CO.19", "SALE CO.20", "SALE CO.21", "SALE CO.22", "SALE CO.23", "SALE CO.24", "SALE CO.25"}),
        #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"SALE CO.1", Int64.Type}, {"SALE CO.2", type text}, {"SALE CO.3", type text}, {"SALE CO.4", type text}, {"SALE CO.5", type text}, {"SALE CO.6", type text}, {"SALE CO.7", type text}, {"SALE CO.8", type text}, {"SALE CO.9", type text}, {"SALE CO.10", type text}, {"SALE CO.11", type text}, {"SALE CO.12", type text}, {"SALE CO.13", type text}, {"SALE CO.14", type text}, {"SALE CO.15", type text}, {"SALE CO.16", type text}, {"SALE CO.17", type text}, {"SALE CO.18", type text}, {"SALE CO.19", type text}, {"SALE CO.20", type text}, {"SALE CO.21", type text}, {"SALE CO.22", type text}, {"SALE CO.23", type text}, {"SALE CO.24", type text}, {"SALE CO.25", type text}, {"NAME_1", type text}, {"DATE_2", type text}, {"NAME_3", type text}, {"CONTROL_4", type text}}),
        #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1","REFER",Splitter.SplitTextByDelimiter(" "),{"REFER.1", "REFER.2", "REFER.3", "REFER.4", "REFER.5", "REFER.6", "REFER.7", "REFER.8", "REFER.9", "REFER.10", "REFER.11", "REFER.12", "REFER.13", "REFER.14", "REFER.15", "REFER.16", "REFER.17", "REFER.18", "REFER.19", "REFER.20", "REFER.21", "REFER.22", "REFER.23", "REFER.24", "REFER.25"}),
        #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"REFER.1", type text}, {"REFER.2", type text}, {"REFER.3", type text}, {"REFER.4", type text}, {"REFER.5", type text}, {"REFER.6", type text}, {"REFER.7", type text}, {"REFER.8", type text}, {"REFER.9", type text}, {"REFER.10", type text}, {"REFER.11", type text}, {"REFER.12", type text}, {"REFER.13", type text}, {"REFER.14", type text}, {"REFER.15", type text}, {"REFER.16", type text}, {"REFER.17", type text}, {"REFER.18", type text}, {"REFER.19", type text}, {"REFER.20", type text}, {"REFER.21", type text}, {"REFER.22", type text}, {"REFER.23", type text}, {"REFER.24", type text}, {"REFER.25", type text}}),
        #"Split Column by Delimiter2" = Table.SplitColumn(#"Changed Type2","JRNL CO",Splitter.SplitTextByDelimiter(" "),{"JRNL CO.1", "JRNL CO.2", "JRNL CO.3", "JRNL CO.4", "JRNL CO.5", "JRNL CO.6", "JRNL CO.7", "JRNL CO.8", "JRNL CO.9", "JRNL CO.10", "JRNL CO.11", "JRNL CO.12", "JRNL CO.13", "JRNL CO.14", "JRNL CO.15", "JRNL CO.16", "JRNL CO.17", "JRNL CO.18", "JRNL CO.19", "JRNL CO.20", "JRNL CO.21", "JRNL CO.22", "JRNL CO.23", "JRNL CO.24", "JRNL CO.25"}),
        #"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"JRNL CO.1", Int64.Type}, {"JRNL CO.2", type text}, {"JRNL CO.3", type text}, {"JRNL CO.4", type text}, {"JRNL CO.5", type text}, {"JRNL CO.6", type text}, {"JRNL CO.7", type text}, {"JRNL CO.8", type text}, {"JRNL CO.9", type text}, {"JRNL CO.10", type text}, {"JRNL CO.11", type text}, {"JRNL CO.12", type text}, {"JRNL CO.13", type text}, {"JRNL CO.14", type text}, {"JRNL CO.15", type text}, {"JRNL CO.16", type text}, {"JRNL CO.17", type text}, {"JRNL CO.18", type text}, {"JRNL CO.19", type text}, {"JRNL CO.20", type text}, {"JRNL CO.21", type text}, {"JRNL CO.22", type text}, {"JRNL CO.23", type text}, {"JRNL CO.24", type text}, {"JRNL CO.25", type text}}),
        #"Split Column by Delimiter3" = Table.SplitColumn(#"Changed Type3","SO",Splitter.SplitTextByDelimiter(" "),{"SO.1", "SO.2", "SO.3", "SO.4", "SO.5", "SO.6", "SO.7", "SO.8", "SO.9", "SO.10", "SO.11", "SO.12", "SO.13", "SO.14", "SO.15", "SO.16", "SO.17", "SO.18", "SO.19", "SO.20", "SO.21", "SO.22", "SO.23", "SO.24", "SO.25"}),
        #"Changed Type4" = Table.TransformColumnTypes(#"Split Column by Delimiter3",{{"SO.1", Int64.Type}, {"SO.2", type text}, {"SO.3", type text}, {"SO.4", type text}, {"SO.5", type text}, {"SO.6", type text}, {"SO.7", type text}, {"SO.8", type text}, {"SO.9", type text}, {"SO.10", type text}, {"SO.11", type text}, {"SO.12", type text}, {"SO.13", type text}, {"SO.14", type text}, {"SO.15", type text}, {"SO.16", type text}, {"SO.17", type text}, {"SO.18", type text}, {"SO.19", type text}, {"SO.20", type text}, {"SO.21", type text}, {"SO.22", type text}, {"SO.23", type text}, {"SO.24", type text}, {"SO.25", type text}}),
        #"Split Column by Delimiter4" = Table.SplitColumn(#"Changed Type4","ODATE",Splitter.SplitTextByDelimiter(" "),{"ODATE.1", "ODATE.2", "ODATE.3", "ODATE.4", "ODATE.5", "ODATE.6", "ODATE.7", "ODATE.8", "ODATE.9", "ODATE.10", "ODATE.11", "ODATE.12", "ODATE.13", "ODATE.14", "ODATE.15", "ODATE.16", "ODATE.17", "ODATE.18", "ODATE.19", "ODATE.20", "ODATE.21", "ODATE.22", "ODATE.23", "ODATE.24", "ODATE.25"}),
        #"Changed Type5" = Table.TransformColumnTypes(#"Split Column by Delimiter4",{{"ODATE.1", type date}, {"ODATE.2", type text}, {"ODATE.3", type text}, {"ODATE.4", type text}, {"ODATE.5", type text}, {"ODATE.6", type text}, {"ODATE.7", type text}, {"ODATE.8", type text}, {"ODATE.9", type text}, {"ODATE.10", type text}, {"ODATE.11", type text}, {"ODATE.12", type text}, {"ODATE.13", type text}, {"ODATE.14", type text}, {"ODATE.15", type text}, {"ODATE.16", type text}, {"ODATE.17", type text}, {"ODATE.18", type text}, {"ODATE.19", type text}, {"ODATE.20", type text}, {"ODATE.21", type text}, {"ODATE.22", type text}, {"ODATE.23", type text}, {"ODATE.24", type text}, {"ODATE.25", type text}}),
        #"Split Column by Delimiter5" = Table.SplitColumn(#"Changed Type5","JRNL TYPE",Splitter.SplitTextByDelimiter(" "),{"JRNL TYPE.1", "JRNL TYPE.2", "JRNL TYPE.3", "JRNL TYPE.4", "JRNL TYPE.5", "JRNL TYPE.6", "JRNL TYPE.7", "JRNL TYPE.8", "JRNL TYPE.9", "JRNL TYPE.10", "JRNL TYPE.11", "JRNL TYPE.12", "JRNL TYPE.13", "JRNL TYPE.14", "JRNL TYPE.15", "JRNL TYPE.16", "JRNL TYPE.17", "JRNL TYPE.18", "JRNL TYPE.19", "JRNL TYPE.20", "JRNL TYPE.21", "JRNL TYPE.22", "JRNL TYPE.23", "JRNL TYPE.24", "JRNL TYPE.25"}),
        #"Changed Type6" = Table.TransformColumnTypes(#"Split Column by Delimiter5",{{"JRNL TYPE.1", type text}, {"JRNL TYPE.2", type text}, {"JRNL TYPE.3", type text}, {"JRNL TYPE.4", type text}, {"JRNL TYPE.5", type text}, {"JRNL TYPE.6", type text}, {"JRNL TYPE.7", type text}, {"JRNL TYPE.8", type text}, {"JRNL TYPE.9", type text}, {"JRNL TYPE.10", type text}, {"JRNL TYPE.11", type text}, {"JRNL TYPE.12", type text}, {"JRNL TYPE.13", type text}, {"JRNL TYPE.14", type text}, {"JRNL TYPE.15", type text}, {"JRNL TYPE.16", type text}, {"JRNL TYPE.17", type text}, {"JRNL TYPE.18", type text}, {"JRNL TYPE.19", type text}, {"JRNL TYPE.20", type text}, {"JRNL TYPE.21", type text}, {"JRNL TYPE.22", type text}, {"JRNL TYPE.23", type text}, {"JRNL TYPE.24", type text}, {"JRNL TYPE.25", type text}}),
        #"Split Column by Delimiter6" = Table.SplitColumn(#"Changed Type6","DATE",Splitter.SplitTextByDelimiter(" "),{"DATE.1", "DATE.2", "DATE.3", "DATE.4", "DATE.5", "DATE.6", "DATE.7", "DATE.8", "DATE.9", "DATE.10", "DATE.11", "DATE.12", "DATE.13", "DATE.14", "DATE.15", "DATE.16", "DATE.17", "DATE.18", "DATE.19", "DATE.20", "DATE.21", "DATE.22", "DATE.23", "DATE.24", "DATE.25"}),
        #"Changed Type7" = Table.TransformColumnTypes(#"Split Column by Delimiter6",{{"DATE.1", type date}, {"DATE.2", type text}, {"DATE.3", type text}, {"DATE.4", type text}, {"DATE.5", type text}, {"DATE.6", type text}, {"DATE.7", type text}, {"DATE.8", type text}, {"DATE.9", type text}, {"DATE.10", type text}, {"DATE.11", type text}, {"DATE.12", type text}, {"DATE.13", type text}, {"DATE.14", type text}, {"DATE.15", type text}, {"DATE.16", type text}, {"DATE.17", type text}, {"DATE.18", type text}, {"DATE.19", type text}, {"DATE.20", type text}, {"DATE.21", type text}, {"DATE.22", type text}, {"DATE.23", type text}, {"DATE.24", type text}, {"DATE.25", type text}}),
        #"Reordered Columns" = Table.ReorderColumns(#"Changed Type7",{"AGME.CO.ID", "SCHED", "NAME", "NAME_1", "N-CD", "SALE CO.1", "SALE CO.2", "SALE CO.3", "SALE CO.4", "SALE CO.5", "SALE CO.6", "SALE CO.7", "SALE CO.8", "SALE CO.9", "SALE CO.10", "SALE CO.11", "SALE CO.12", "SALE CO.13", "SALE CO.14", "SALE CO.15", "SALE CO.16", "SALE CO.17", "SALE CO.18", "SALE CO.19", "SALE CO.20", "SALE CO.21", "SALE CO.22", "SALE CO.23", "SALE CO.24", "SALE CO.25", "REFER.1", "REFER.2", "REFER.3", "REFER.4", "REFER.5", "REFER.6", "REFER.7", "REFER.8", "REFER.9", "REFER.10", "REFER.11", "REFER.12", "REFER.13", "REFER.14", "REFER.15", "REFER.16", "REFER.17", "REFER.18", "REFER.19", "REFER.20", "REFER.21", "REFER.22", "REFER.23", "REFER.24", "REFER.25", "JRNL CO.1", "JRNL CO.2", "JRNL CO.3", "JRNL CO.4", "JRNL CO.5", "JRNL CO.6", "JRNL CO.7", "JRNL CO.8", "JRNL CO.9", "JRNL CO.10", "JRNL CO.11", "JRNL CO.12", "JRNL CO.13", "JRNL CO.14", "JRNL CO.15", "JRNL CO.16", "JRNL CO.17", "JRNL CO.18", "JRNL CO.19", "JRNL CO.20", "JRNL CO.21", "JRNL CO.22", "JRNL CO.23", "JRNL CO.24", "JRNL CO.25", "SO.1", "SO.2", "SO.3", "SO.4", "SO.5", "SO.6", "SO.7", "SO.8", "SO.9", "SO.10", "SO.11", "SO.12", "SO.13", "SO.14", "SO.15", "SO.16", "SO.17", "SO.18", "SO.19", "SO.20", "SO.21", "SO.22", "SO.23", "SO.24", "SO.25", "ODATE.1", "ODATE.2", "ODATE.3", "ODATE.4", "ODATE.5", "ODATE.6", "ODATE.7", "ODATE.8", "ODATE.9", "ODATE.10", "ODATE.11", "ODATE.12", "ODATE.13", "ODATE.14", "ODATE.15", "ODATE.16", "ODATE.17", "ODATE.18", "ODATE.19", "ODATE.20", "ODATE.21", "ODATE.22", "ODATE.23", "ODATE.24", "ODATE.25", "JRNL TYPE.1", "JRNL TYPE.2", "JRNL TYPE.3", "JRNL TYPE.4", "JRNL TYPE.5", "JRNL TYPE.6", "JRNL TYPE.7", "JRNL TYPE.8", "JRNL TYPE.9", "JRNL TYPE.10", "JRNL TYPE.11", "JRNL TYPE.12", "JRNL TYPE.13", "JRNL TYPE.14", "JRNL TYPE.15", "JRNL TYPE.16", "JRNL TYPE.17", "JRNL TYPE.18", "JRNL TYPE.19", "JRNL TYPE.20", "JRNL TYPE.21", "JRNL TYPE.22", "JRNL TYPE.23", "JRNL TYPE.24", "JRNL TYPE.25", "CONTROL", "DATE.1", "DATE.2", "DATE.3", "DATE.4", "DATE.5", "DATE.6", "DATE.7", "DATE.8", "DATE.9", "DATE.10", "DATE.11", "DATE.12", "DATE.13", "DATE.14", "DATE.15", "DATE.16", "DATE.17", "DATE.18", "DATE.19", "DATE.20", "DATE.21", "DATE.22", "DATE.23", "DATE.24", "DATE.25", "DATE_2", "A-ACCT", "NAME_3", "CONTROL_4", "CONTROL2"}),
        #"Removed Columns1" = Table.RemoveColumns(#"Reordered Columns",{"DATE_2", "A-ACCT", "NAME_3", "CONTROL_4", "CONTROL2"}),
        #"Reordered Columns1" = Table.ReorderColumns(#"Removed Columns1",{"AGME.CO.ID", "SCHED", "NAME", "NAME_1", "N-CD", "CONTROL", "SALE CO.1", "SALE CO.2", "SALE CO.3", "SALE CO.4", "SALE CO.5", "SALE CO.6", "SALE CO.7", "SALE CO.8", "SALE CO.9", "SALE CO.10", "SALE CO.11", "SALE CO.12", "SALE CO.13", "SALE CO.14", "SALE CO.15", "SALE CO.16", "SALE CO.17", "SALE CO.18", "SALE CO.19", "SALE CO.20", "SALE CO.21", "SALE CO.22", "SALE CO.23", "SALE CO.24", "SALE CO.25", "REFER.1", "REFER.2", "REFER.3", "REFER.4", "REFER.5", "REFER.6", "REFER.7", "REFER.8", "REFER.9", "REFER.10", "REFER.11", "REFER.12", "REFER.13", "REFER.14", "REFER.15", "REFER.16", "REFER.17", "REFER.18", "REFER.19", "REFER.20", "REFER.21", "REFER.22", "REFER.23", "REFER.24", "REFER.25", "JRNL CO.1", "JRNL CO.2", "JRNL CO.3", "JRNL CO.4", "JRNL CO.5", "JRNL CO.6", "JRNL CO.7", "JRNL CO.8", "JRNL CO.9", "JRNL CO.10", "JRNL CO.11", "JRNL CO.12", "JRNL CO.13", "JRNL CO.14", "JRNL CO.15", "JRNL CO.16", "JRNL CO.17", "JRNL CO.18", "JRNL CO.19", "JRNL CO.20", "JRNL CO.21", "JRNL CO.22", "JRNL CO.23", "JRNL CO.24", "JRNL CO.25", "SO.1", "SO.2", "SO.3", "SO.4", "SO.5", "SO.6", "SO.7", "SO.8", "SO.9", "SO.10", "SO.11", "SO.12", "SO.13", "SO.14", "SO.15", "SO.16", "SO.17", "SO.18", "SO.19", "SO.20", "SO.21", "SO.22", "SO.23", "SO.24", "SO.25", "ODATE.1", "ODATE.2", "ODATE.3", "ODATE.4", "ODATE.5", "ODATE.6", "ODATE.7", "ODATE.8", "ODATE.9", "ODATE.10", "ODATE.11", "ODATE.12", "ODATE.13", "ODATE.14", "ODATE.15", "ODATE.16", "ODATE.17", "ODATE.18", "ODATE.19", "ODATE.20", "ODATE.21", "ODATE.22", "ODATE.23", "ODATE.24", "ODATE.25", "JRNL TYPE.1", "JRNL TYPE.2", "JRNL TYPE.3", "JRNL TYPE.4", "JRNL TYPE.5", "JRNL TYPE.6", "JRNL TYPE.7", "JRNL TYPE.8", "JRNL TYPE.9", "JRNL TYPE.10", "JRNL TYPE.11", "JRNL TYPE.12", "JRNL TYPE.13", "JRNL TYPE.14", "JRNL TYPE.15", "JRNL TYPE.16", "JRNL TYPE.17", "JRNL TYPE.18", "JRNL TYPE.19", "JRNL TYPE.20", "JRNL TYPE.21", "JRNL TYPE.22", "JRNL TYPE.23", "JRNL TYPE.24", "JRNL TYPE.25", "DATE.1", "DATE.2", "DATE.3", "DATE.4", "DATE.5", "DATE.6", "DATE.7", "DATE.8", "DATE.9", "DATE.10", "DATE.11", "DATE.12", "DATE.13", "DATE.14", "DATE.15", "DATE.16", "DATE.17", "DATE.18", "DATE.19", "DATE.20", "DATE.21", "DATE.22", "DATE.23", "DATE.24", "DATE.25"}),
        #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Reordered Columns1", {"AGME.CO.ID", "SCHED", "NAME", "NAME_1", "N-CD", "CONTROL"}, "Attribute", "Value"),
        #"Replaced Value" = Table.ReplaceValue(#"Unpivoted Columns",".*","",Replacer.ReplaceText,{"Attribute"}),
        #"Split Column by Delimiter7" = Table.SplitColumn(#"Replaced Value","Attribute",Splitter.SplitTextByDelimiter("."),{"Attribute.1", "Attribute.2"}),
        #"Changed Type8" = Table.TransformColumnTypes(#"Split Column by Delimiter7",{{"Attribute.1", type text}, {"Attribute.2", Int64.Type}}),
        #"Removed Columns2" = Table.RemoveColumns(#"Changed Type8",{"Attribute.2"}),
        #"Renamed Columns" = Table.RenameColumns(#"Removed Columns2",{{"Attribute.1", "Type"}}),
        #"Removed Columns3" = Table.RemoveColumns(#"Renamed Columns",{"SCHED", "NAME", "NAME_1", "N-CD"}),
        #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns3",{{"AGME.CO.ID", "companyid"}, {"CONTROL", "control"}}),
        #"Reordered Columns2" = Table.ReorderColumns(#"Renamed Columns1",{"companyid", "control", "Type", "Value"})
    in
        #"Reordered Columns2"

    I need to do one more transform which is a Pivot like so:

    #"Pivoted Column" = Table.Pivot(#"Reordered Columns2", List.Distinct(#"Reordered Columns2"[Type]), "Type", "Value")
    in
        #"Pivoted Column"

    and I only get 326 rows. Because it is a pivot without aggregation I get errors but even if I remove errors I still don't get the complete result set.

    Any ideas?

    Monday, January 12, 2015 7:34 PM

Answers

  • Hi John Donelly

    Transform data with 45,000 rows when occur errors that is not easy to see and check in power query windows due to the limit loading data in grid view. Power Query will be ignore error rows to loading. My opinion, you need to check carefully errors step by step. For example, after you do to split column by delimiter function. You need to close power query window and checking in excel whether having errors or not. if losing rows, turn back to power query windows to check errors by using to add new columns with formula : try [Checking column name] . This function will be return the column contains records. You can expand this column by clicking and select "HasError". Then filter the new columns with TRUE condition. You can see the errors rows then fixed it..and continue other step and make sure you can load full data before with Pivot function.

    Regards,

    Tuesday, January 13, 2015 4:50 AM
  • Hi,

    Never mind, I understand. We sometime can't transfer full information through forum. As your description, we just revise some formulas by grouping Control No before going to transform data. You can use below code :

        InsertedCustom = Table.AddColumn(#"Renamed Columns", "Control No", each if [Attribute] = "CONTROL" then [Value] else null),
        FillUp = Table.FillDown(InsertedCustom,{"Control No"}),
        GroupControl = Table.Group(FillUp, "Control No", {"Count", each _[ [Attribute], [Value] ], type table} ),
        GroupAttribute = Table.TransformColumns( GroupControl, {"Count", (tbl) => Table.Group( tbl, "Attribute", { "Item", each _ } )   } ),
        Expand = Table.ExpandTableColumn(GroupAttribute, "Count", {"Attribute", "Item"}, {"Attribute", "Item"}),
        Transform = Table.TransformColumns( Expand, { "Item" , (tbl) => Table.DemoteHeaders( Table.RenameColumns( tbl, { "Value", tbl{0}[Attribute] } ) ) [Column2] } ),
        GroupedRows = Table.Combine( Table.Group(Transform, {"Control No"}, {{"Count", each Table.PromoteHeaders(Table.FromColumns(_[Item]) ) , type table}}) [Count] ),
        FillDown = Table.FillDown(GroupedRows,{"CONTROL"})
    in
        FillDown

    I also share sample for more detail

    https://onedrive.live.com/edit.aspx?cid=94B99C319E63236A&resid=94B99C319E63236A%21344&app=Excel

    Regards,


    • Marked as answer by johndonnelly Friday, January 16, 2015 4:41 PM
    Friday, January 16, 2015 3:01 AM

All replies

  • Hi John Donelly

    Transform data with 45,000 rows when occur errors that is not easy to see and check in power query windows due to the limit loading data in grid view. Power Query will be ignore error rows to loading. My opinion, you need to check carefully errors step by step. For example, after you do to split column by delimiter function. You need to close power query window and checking in excel whether having errors or not. if losing rows, turn back to power query windows to check errors by using to add new columns with formula : try [Checking column name] . This function will be return the column contains records. You can expand this column by clicking and select "HasError". Then filter the new columns with TRUE condition. You can see the errors rows then fixed it..and continue other step and make sure you can load full data before with Pivot function.

    Regards,

    Tuesday, January 13, 2015 4:50 AM
  • Hello CDZung,

    Thanks for the response. I did what you said which was great advice. I was able to get everything to work up to the Pivot and the picture below shows the error. I think there are too many rows to pivot.


    John Donnelly

    Tuesday, January 13, 2015 8:51 PM
  • Hello John Donenelly,

    I think your database have double entry (records). When you make pivot function without aggregation function parameter that will be make errors. You try to correct your Pivot formula line by adding more List.First ( if Value column is text) or List.Sum ( if it's numbers) as below :

    Table.Pivot(#"Reordered Columns2", List.Distinct(#"Reordered Columns2"[Type]), "Type", "Value", List.First / List.Sum )

    Regards,


    • Edited by CDzung Wednesday, January 14, 2015 4:31 AM
    Wednesday, January 14, 2015 4:17 AM
  • Hello again and thanks for your help. I used List.First because the value column is text however I only got one row returned so I thought I would give you all my code (because I changed some things to make it simpler) and some pictures. Here's my code:

    let
        Source = Csv.Document(File.Contents("C:\Users\jdonnelly\Desktop\2014 JW Annual Review\ALL STORES 01-07-15 AR OPEN.csv"),null,",",null,1252),
        #"First Row as Header" = Table.PromoteHeaders(Source),
        #"Changed Type" = Table.TransformColumnTypes(#"First Row as Header",{{"AR-OPEN", type text}, {"SALE CO", type text}, {"NAME", type text}, {"N-CD", Int64.Type}, {"REFER", type text}, {"JRNL CO", type text}, {"SCHED", type text}, {"AGME.CO.ID", Int64.Type}, {"SO", type text}, {"ODATE", type text}, {"JRNL TYPE", type text}, {"CONTROL", type text}, {"DATE", type text}, {"A-ACCT", type text}, {"CONTROL2", type text}}),
        #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"AR-OPEN", "NAME", "NAME_1", "N-CD", "JRNL CO", "SCHED", "AGME.CO.ID", "JRNL TYPE", "DATE", "DATE_2", "A-ACCT", "NAME_3", "CONTROL_4", "CONTROL2"}),
        #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"CONTROL", "SALE CO", "REFER", "SO", "ODATE"}),
        #"Split Column by Delimiter" = Table.SplitColumn(#"Reordered Columns","ODATE",Splitter.SplitTextByDelimiter(" "),{"ODATE.1", "ODATE.2", "ODATE.3", "ODATE.4", "ODATE.5", "ODATE.6", "ODATE.7", "ODATE.8", "ODATE.9", "ODATE.10", "ODATE.11", "ODATE.12", "ODATE.13", "ODATE.14", "ODATE.15", "ODATE.16", "ODATE.17", "ODATE.18", "ODATE.19"}),
        #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"ODATE.1", type date}, {"ODATE.2", type text}, {"ODATE.3", type text}, {"ODATE.4", type text}, {"ODATE.5", type text}, {"ODATE.6", type text}, {"ODATE.7", type text}, {"ODATE.8", type text}, {"ODATE.9", type text}, {"ODATE.10", type text}, {"ODATE.11", type text}, {"ODATE.12", type text}, {"ODATE.13", type text}, {"ODATE.14", type text}, {"ODATE.15", type text}, {"ODATE.16", type text}, {"ODATE.17", type text}, {"ODATE.18", type text}, {"ODATE.19", type text}}),
        #"Removed Columns1" = Table.RemoveColumns(#"Changed Type1",{"ODATE.2", "ODATE.3", "ODATE.5", "ODATE.6", "ODATE.8", "ODATE.9", "ODATE.11", "ODATE.12", "ODATE.14", "ODATE.15", "ODATE.17", "ODATE.18"}),
        #"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns1",{{"ODATE.1", type date}, {"ODATE.4", type date}, {"ODATE.7", type date}, {"ODATE.10", type date}, {"ODATE.13", type date}, {"ODATE.16", type date}, {"ODATE.19", type date}}),
        #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type2","SO",Splitter.SplitTextByDelimiter(" "),{"SO.1", "SO.2", "SO.3", "SO.4", "SO.5", "SO.6", "SO.7", "SO.8", "SO.9", "SO.10", "SO.11", "SO.12", "SO.13", "SO.14", "SO.15", "SO.16", "SO.17", "SO.18", "SO.19"}),
        #"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"SO.1", Int64.Type}, {"SO.2", type text}, {"SO.3", type text}, {"SO.4", type text}, {"SO.5", type text}, {"SO.6", type text}, {"SO.7", type text}, {"SO.8", type text}, {"SO.9", type text}, {"SO.10", type text}, {"SO.11", type text}, {"SO.12", type text}, {"SO.13", type text}, {"SO.14", type text}, {"SO.15", type text}, {"SO.16", type text}, {"SO.17", type text}, {"SO.18", type text}, {"SO.19", type text}}),
        #"Removed Columns2" = Table.RemoveColumns(#"Changed Type3",{"SO.2", "SO.3", "SO.5", "SO.6", "SO.8", "SO.9", "SO.11", "SO.12", "SO.14", "SO.15", "SO.17", "SO.18"}),
        #"Split Column by Delimiter2" = Table.SplitColumn(#"Removed Columns2","REFER",Splitter.SplitTextByDelimiter(" "),{"REFER.1", "REFER.2", "REFER.3", "REFER.4", "REFER.5", "REFER.6", "REFER.7", "REFER.8", "REFER.9", "REFER.10", "REFER.11", "REFER.12", "REFER.13", "REFER.14", "REFER.15", "REFER.16", "REFER.17", "REFER.18", "REFER.19"}),
        #"Changed Type4" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"REFER.1", type text}, {"REFER.2", type text}, {"REFER.3", type text}, {"REFER.4", type text}, {"REFER.5", type text}, {"REFER.6", type text}, {"REFER.7", type text}, {"REFER.8", type text}, {"REFER.9", type text}, {"REFER.10", type text}, {"REFER.11", type text}, {"REFER.12", type text}, {"REFER.13", type text}, {"REFER.14", type text}, {"REFER.15", type text}, {"REFER.16", type text}, {"REFER.17", type text}, {"REFER.18", type text}, {"REFER.19", type text}}),
        #"Removed Columns3" = Table.RemoveColumns(#"Changed Type4",{"REFER.2", "REFER.3", "REFER.5", "REFER.6", "REFER.8", "REFER.9", "REFER.11", "REFER.12", "REFER.14", "REFER.15", "REFER.17", "REFER.18"}),
        #"Split Column by Delimiter3" = Table.SplitColumn(#"Removed Columns3","SALE CO",Splitter.SplitTextByDelimiter(" "),{"SALE CO.1", "SALE CO.2", "SALE CO.3", "SALE CO.4", "SALE CO.5", "SALE CO.6", "SALE CO.7", "SALE CO.8", "SALE CO.9", "SALE CO.10", "SALE CO.11", "SALE CO.12", "SALE CO.13", "SALE CO.14", "SALE CO.15", "SALE CO.16", "SALE CO.17", "SALE CO.18", "SALE CO.19"}),
        #"Changed Type5" = Table.TransformColumnTypes(#"Split Column by Delimiter3",{{"SALE CO.1", Int64.Type}, {"SALE CO.2", type text}, {"SALE CO.3", type text}, {"SALE CO.4", type text}, {"SALE CO.5", type text}, {"SALE CO.6", type text}, {"SALE CO.7", type text}, {"SALE CO.8", type text}, {"SALE CO.9", type text}, {"SALE CO.10", type text}, {"SALE CO.11", type text}, {"SALE CO.12", type text}, {"SALE CO.13", type text}, {"SALE CO.14", type text}, {"SALE CO.15", type text}, {"SALE CO.16", type text}, {"SALE CO.17", type text}, {"SALE CO.18", type text}, {"SALE CO.19", type text}}),
        #"Removed Columns4" = Table.RemoveColumns(#"Changed Type5",{"SALE CO.2", "SALE CO.3", "SALE CO.5", "SALE CO.6", "SALE CO.8", "SALE CO.9", "SALE CO.11", "SALE CO.12", "SALE CO.14", "SALE CO.15", "SALE CO.17", "SALE CO.18"}),
        #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns4", {}, "Attribute", "Value"),
        #"Changed Type6" = Table.TransformColumnTypes(#"Unpivoted Columns",{{"Value", type text}}),
        #"Split Column by Delimiter4" = Table.SplitColumn(#"Changed Type6","Attribute",Splitter.SplitTextByDelimiter("."),{"Attribute.1", "Attribute.2"}),
        #"Changed Type7" = Table.TransformColumnTypes(#"Split Column by Delimiter4",{{"Attribute.1", type text}, {"Attribute.2", type text}}),
        #"Removed Columns5" = Table.RemoveColumns(#"Changed Type7",{"Attribute.2"}),
        #"Renamed Columns" = Table.RenameColumns(#"Removed Columns5",{{"Attribute.1", "Attribute"}})
        #"Pivoted Column" = Table.Pivot(#"Renamed Columns", List.Distinct(#"Renamed Columns"[Attribute]), "Attribute", "Value", List.First)
    in
        #"Pivoted Column"

    Before the Table.Pivot my View is as follows:

    It runs down to row 18,200 or so. I chose a smaller file to make things simpler.

    After the Table.Pivot as written above this is what my view shows:

    So I received only one row. Is there a way to make it all rows?

    (For background: the CSV file is from a PICK database that has multi-variate fields where all but one of the fields has multiple values correlated with multiple values in other fields.)

    Thanks again


    John Donnelly


    • Edited by johndonnelly Wednesday, January 14, 2015 9:19 PM Removed our domain name from code
    Wednesday, January 14, 2015 9:18 PM
  • Hi John Donnelly,

    As your database can't use Pivot function. We must use some transform functions of power query. You can use below code to replace from pivot function line in advanced editor windows :

        Group = Table.Group(#"Renamed Columns", {"Attribute"}, {{"Count", each _, type table}}),
        Transform = Table.TransformColumns( Group, {"Count", (tbl)=>Table.RemoveColumns( Table.DemoteHeaders( Table.RenameColumns( tbl, {"Value", tbl{0}[Attribute] } ) ), "Column1" ) } ) [Count],
        Result = Table.PromoteHeaders( Table.FromColumns( List.Transform( Transform, each Table.ToList( _, each _{0} ) ) ) )
    in
        Result

    I made a sample file and break down code into step by step :

    https://onedrive.live.com/edit.aspx?cid=94B99C319E63236A&resid=94B99C319E63236A%21340&app=Excel

    I hope this help you

    Regards,

     
    Thursday, January 15, 2015 2:13 AM
  • Hello CDZung,

    Thank you for the code. It is very helpful to see it in the workbook as well.

    I know this may be frustrating and I apologize but I have another problem. 

    The data has a glitch in that each row of the ultimate Result should have a CONTROL value. Any SALE CO, REFER, SO and ODATE following a CONTROL needs to be on separate rows with the CONTROL number repeated.

    The results of your query gives a row for each CONTROL and gives all the other rows at the end with a blank CONTROL field. Here is a picture of a CONTROL with multiple rows below it:

    You can see CONTROL 5026 should apply to all the rows up to the CONTROL that is 7059.

    So if I look at the Result set from your query and go to the end of values in the CONTROL column we see:

    There are 2,828 rows in the original CSV file, one row for each CONTROL. So I have these orphan rows.

    Any ideas on this issue?

    I really appreciate your help and I know that I am pushing the limits of reasonable questions here.

    Thanks again,


    John Donnelly

    Thursday, January 15, 2015 4:21 PM
  • Hi,

    Never mind, I understand. We sometime can't transfer full information through forum. As your description, we just revise some formulas by grouping Control No before going to transform data. You can use below code :

        InsertedCustom = Table.AddColumn(#"Renamed Columns", "Control No", each if [Attribute] = "CONTROL" then [Value] else null),
        FillUp = Table.FillDown(InsertedCustom,{"Control No"}),
        GroupControl = Table.Group(FillUp, "Control No", {"Count", each _[ [Attribute], [Value] ], type table} ),
        GroupAttribute = Table.TransformColumns( GroupControl, {"Count", (tbl) => Table.Group( tbl, "Attribute", { "Item", each _ } )   } ),
        Expand = Table.ExpandTableColumn(GroupAttribute, "Count", {"Attribute", "Item"}, {"Attribute", "Item"}),
        Transform = Table.TransformColumns( Expand, { "Item" , (tbl) => Table.DemoteHeaders( Table.RenameColumns( tbl, { "Value", tbl{0}[Attribute] } ) ) [Column2] } ),
        GroupedRows = Table.Combine( Table.Group(Transform, {"Control No"}, {{"Count", each Table.PromoteHeaders(Table.FromColumns(_[Item]) ) , type table}}) [Count] ),
        FillDown = Table.FillDown(GroupedRows,{"CONTROL"})
    in
        FillDown

    I also share sample for more detail

    https://onedrive.live.com/edit.aspx?cid=94B99C319E63236A&resid=94B99C319E63236A%21344&app=Excel

    Regards,


    • Marked as answer by johndonnelly Friday, January 16, 2015 4:41 PM
    Friday, January 16, 2015 3:01 AM
  • Hello CDZung,

    Thank you for the help. This is perfect. I will now study this query to try and understand what is happening.

    The future of self-service BI is bright with Power Query and the "M" language.

    Best Regards,


    John Donnelly

    Friday, January 16, 2015 4:40 PM