none
I am getting nulls when I pivot my data in Power Query. RRS feed

  • Question

  • Here is my advanced editor:

    let
        Source = Excel.Workbook(File.Contents("C:\filepath\filename.xlsx"), null, true),
        Sale_Sheet = Source{[Item="Sale",Kind="Sheet"]}[Data],
        #"Promoted Headers" = Table.PromoteHeaders(Sale_Sheet, [PromoteAllScalars=true]),
        #"Removed Columns" = Table.RemoveColumns(#"Promoted Headers",{"Posted in", "Document Date", "TpO", "Project 1", "Client (number)", "Name Client", "Account", "Profit Center", "Before Profit Center", "Object", "Activity", "Object Name", "Efet. Date", "Denom.", "Product", "Quantities (hours)", "Total", "Currency"}),
        #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Year", "Project", "Account Name"}, "Attribute", "Value"),
        #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter("(", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
        #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Split Column by Delimiter", {{"Attribute.2", each Text.BeforeDelimiter(_, ")"), type text}}),
        #"Renamed Columns" = Table.RenameColumns(#"Extracted Text Before Delimiter",{{"Attribute.2", "Month"}}),
        #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Year", "Project", "Month", "Account Name", "Attribute.1", "Value"}),
        #"Merged Columns" = Table.CombineColumns(#"Reordered Columns",{"Account Name", "Attribute.1"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged"),
        #"Changed Type" = Table.TransformColumnTypes(#"Merged Columns",{{"Value", type number}, {"Project", Int64.Type}, {"Year", Int64.Type}}),
        #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Merged] = "Maintenance Expense,Total" or [Merged] = "Office Expense,Total" or [Merged] = "perdiems,Total" or [Merged] = "hours,Total"),
        #"Pivoted Column" = Table.Pivot(#"Filtered Rows", List.Distinct(#"Filtered Rows"[Merged]), "Merged", "Value", List.Sum)
    in
        #"Pivoted Column"
    Wednesday, March 6, 2019 12:45 AM

Answers

  • Hi Christopher. Can you share more details? Without having your source file (or a small sample file with any sensitive data removed, plus the expected output you're trying to produce) it's difficult to determine exactly what the issue is that you're seeing.

    Ehren

    Friday, March 8, 2019 10:46 PM
    Owner