Power Query missing values when expanding tables

Question

I have an issue: when I try to expand table from many files I'm losing some of the values.

What I have figured out so far the problem starts when the query met a empty table.

I have tried to filter out the empty tables but the problem is still there.

Any idea what is a issue and how to resolve it?

enter image description here

Query:

let
    Source = Folder.Files("X:\Operations\tutaj\SLA MIAD"),
    #"Lowercased Text" = Table.TransformColumns(Source,{{"Extension", Text.Lower, type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Lowercased Text", each Text.Contains([Extension], "xlsx")),
    #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each Text.Contains([Name], "NCR") or Text.Contains([Name], "Printec") or Text.Contains([Name], "PTL")),
    #"Filtered Rows2" = Table.SelectRows(#"Filtered Rows1", each Text.Contains([Name], "2018")),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows2",{"Content"}),
    #"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Custom", each Excel.Workbook([Content])),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Name", "Data", "Item", "Kind", "Hidden"}),
    #"Filtered Rows3" = Table.SelectRows(#"Expanded Custom", each ([Kind] = "Sheet")),
    #"Filtered Rows4" = Table.SelectRows(#"Filtered Rows3", each Text.Contains([Name], "SLA report")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows4",{"Name", "Item", "Kind", "Hidden"}),
    #"Removed Errors" = Table.RemoveRowsWithErrors(#"Removed Columns", {"Content"}),
    #"Invoked Custom Function" = Table.AddColumn(#"Removed Errors", "TestFunction", each FileQuery([Data])),
    #"Added Custom1" = Table.AddColumn(#"Invoked Custom Function", "Custom", each Table.IsEmpty([TestFunction])),
    #"Filtered Rows6" = Table.SelectRows(#"Added Custom1", each ([Custom] = false)),
    #"Expanded TestFunction" = Table.ExpandTableColumn(#"Filtered Rows6", "TestFunction", {"Order ID", "ATM", "City", "Country", "CIT/Vault", "Service", "SLACategory", "Condition", "Serv Acc Mon", "Serv Acc Sat", "Serv Acc Sun", "Status", "Reason", "Description", "Created(CET)", "SLA Target Date(CET)", "Closed(CET)", "Age", "Contractual Reaction Time", "Overdue By absolute", "Overdue By Srv.Hrs", "On Time", "Charged call", "Reference ID", "Reference priority", "Comments", "SLA Category"}, {"Order ID", "ATM", "City", "Country", "CIT/Vault", "Service", "SLACategory", "Condition", "Serv Acc Mon", "Serv Acc Sat", "Serv Acc Sun", "Status", "Reason", "Description", "Created(CET)", "SLA Target Date(CET)", "Closed(CET)", "Age", "Contractual Reaction Time", "Overdue By absolute", "Overdue By Srv.Hrs", "On Time", "Charged call", "Reference ID", "Reference priority", "Comments", "SLA Category"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Expanded TestFunction",{"Order ID", "ATM", "City", "Country", "CIT/Vault", "Service", "Condition", "Reason", "Status", "Description", "Created(CET)", "SLA Target Date(CET)", "Closed(CET)", "Overdue By absolute", "Overdue By Srv.Hrs", "On Time"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns1",{{"Created(CET)", type datetime}, {"SLA Target Date(CET)", type datetime}, {"Closed(CET)", type datetime}}),
    #"Added Custom2" = Table.AddColumn(#"Changed Type", "Age", each [#"Closed(CET)"]-[#"Created(CET)"]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom2",{{"Age", type duration}}),
    #"Filtered Rows5" = Table.SelectRows(#"Changed Type1", each [Order ID] <> null and [Order ID] <> "")
in
    #"Filtered Rows5"

@Akber Iqbal , post updated.THe problem starts at this point #"Expanded TestFunction" — Wabanek

chillin chillin · Accepted Answer · 2018-12-28T12:37:15

I think there are some differences in the headers present in your tables (that you want to expand) and the headers you're actually expanding.

You've expanded SLACategory and Order ID (in the top half of your screenshot), but these should probably be SLA Category and OrderId respectively (in the bottom half of your screenshot). Note the differences in spacing and capitalisation.

When you specify a List of headers to expand (between { and } in the below):

#"Expanded TestFunction" = Table.ExpandTableColumn(#"Filtered Rows6", "TestFunction", {"Order ID", "ATM", "City", "Country", "CIT/Vault", "Service", "SLACategory", "Condition", "Serv Acc Mon", "Serv Acc Sat", "Serv Acc Sun", "Status", "Reason", "Description", "Created(CET)", "SLA Target Date(CET)", "Closed(CET)", "Age", "Contractual Reaction Time", "Overdue By absolute", "Overdue By Srv.Hrs", "On Time", "Charged call", "Reference ID", "Reference priority", "Comments", "SLA Category"}, {"Order ID", "ATM", "City", "Country", "CIT/Vault", "Service", "SLACategory", "Condition", "Serv Acc Mon", "Serv Acc Sat", "Serv Acc Sun", "Status", "Reason", "Description", "Created(CET)", "SLA Target Date(CET)", "Closed(CET)", "Age", "Contractual Reaction Time", "Overdue By absolute", "Overdue By Srv.Hrs", "On Time", "Charged call", "Reference ID", "Reference priority", "Comments", "SLA Category"}),

I think you need to either

hard code the headers themselves perfectly (in terms of case sensitivity and spaces)
or expand them dynamically and not based on some hard coded list.

To expand your columns dynamically, you'd have to do something like replacing this line:

#"Expanded TestFunction" = Table.ExpandTableColumn(#"Filtered Rows6", "TestFunction", {"Order ID", "ATM", "City", "Country", "CIT/Vault", "Service", "SLACategory", "Condition", "Serv Acc Mon", "Serv Acc Sat", "Serv Acc Sun", "Status", "Reason", "Description", "Created(CET)", "SLA Target Date(CET)", "Closed(CET)", "Age", "Contractual Reaction Time", "Overdue By absolute", "Overdue By Srv.Hrs", "On Time", "Charged call", "Reference ID", "Reference priority", "Comments", "SLA Category"}, {"Order ID", "ATM", "City", "Country", "CIT/Vault", "Service", "SLACategory", "Condition", "Serv Acc Mon", "Serv Acc Sat", "Serv Acc Sun", "Status", "Reason", "Description", "Created(CET)", "SLA Target Date(CET)", "Closed(CET)", "Age", "Contractual Reaction Time", "Overdue By absolute", "Overdue By Srv.Hrs", "On Time", "Charged call", "Reference ID", "Reference priority", "Comments", "SLA Category"}),

with:

allHeaders = List.Combine(List.Transform(#"Filtered Rows6"[TestFunction], Table.ColumnNames)),
headersToExpand = List.Distinct(allHeaders),
#"Expanded TestFunction" = Table.ExpandTableColumn(#"Filtered Rows6", "TestFunction", headersToExpand),

(You'll need to click "Advanced Editor" in the top left of the Query Editor and replace the code there.)

Power Query missing values when expanding tables

1 Answers