I have a multi file, multi sheet query. Once I have all the data combined, I want to duplicate certain rows from the data set, and append back to the list.. I have managed to create the table of duplicate rows I want, but the append step fails. The error is: Expression.Error: We cannot convert a value of type Table to type List. Details: Value=[Table] Tyep = [Type]. All the steps work until the append step. Below is my code:
let
Source = Folder.Files("U:\LifePoints Reporting\EOM Reporting"),
#"Removed Other Columns" = Table.SelectColumns(Source,{"Content", "Name"}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Removed Other Columns", "Name", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), {"Name.1", "Name.2"}),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Name.1", type text}, {"Name.2", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Name.1"}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Removed Columns", "Name.2", Splitter.SplitTextByEachDelimiter({"."}, QuoteStyle.Csv, true), {"Name.2.1", "Name.2.2"}),
#"Removed Columns1" = Table.RemoveColumns(#"Split Column by Delimiter1",{"Name.2.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns1",{{"Name.2.1", type date}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type1", each Date.IsInPreviousNMonths([Name.2.1], 2)),
#"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows", each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
#"Removed Other Columns1" = Table.SelectColumns(#"Invoke Custom Function1", {"Transform File"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
#"Changed Type2" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Cash Received", type number}, {"Billing Price Home", type number}, {"Balance", type number}, {"Date", type date}, {"Panel", type text}}),
#"Removed Errors" = Table.RemoveRowsWithErrors(#"Changed Type2", {"Date"}),
#"Filtered Rows1" = Table.SelectRows(#"Removed Errors", each true),
#"Filtered Rows2" = Table.SelectRows(#"Filtered Rows1", each Date.IsInPreviousNMonths([Date], 1)),
#"New Table" = Table.SelectRows(#"Filtered Rows2",each [Cash Received] <> null),
#"Removed Columns2" = Table.RemoveColumns(#"New Table",{"Cash Received"}),
#"Append Query" = Table.Combine(#"Filtered Rows2",#"Removed Columns2")
in
#"Append Query"
