Forum Discussion
Like unpivot, but not for repeating columns
- 3 years ago
My #"Renamed Columns" should reference your last step:
let Source = Pdf.Tables( File.Contents( "J:\Accounting\Bank Statements\2022\10-Oct 2022\WF TRH Main Operating 1425-Oct 2022.pdf" ), [Implementation = "1.3"] ), Table005 = Source{[Id = "Table005"]}[Data], #"Promoted Headers1" = Table.PromoteHeaders(Table005, [PromoteAllScalars = true]), #"Changed Type1" = Table.TransformColumnTypes( #"Promoted Headers1", { {"Number", type text}, {"Amount", type number}, {"Date", type date}, {"Number_1", type text}, {"Amount_2", type number}, {"Date_3", type date}, {"Number_4", type text}, {"Amount_5", type number}, {"Date_6", type date} } ), #"Changed Type" = Table.TransformColumnTypes( #"Changed Type1", { {"Number", type text}, {"Amount", Currency.Type}, {"Date", type date}, {"Number_1", type text}, {"Amount_2", Currency.Type}, {"Date_3", type date}, {"Number_4", type text}, {"Amount_5", Currency.Type}, {"Date_6", type date} } ), #"Extracted Text Before Delimiter" = Table.TransformColumns( #"Changed Type", { {"Number", each Text.BeforeDelimiter(_, " *"), type text}, {"Number_1", each Text.BeforeDelimiter(_, " *"), type text}, {"Number_4", each Text.BeforeDelimiter(_, " *"), type text} } ), #"Renamed Columns" = Table.RenameColumns( #"Extracted Text Before Delimiter", { {"Date", "Date_1"}, {"Date_3", "Date_2"}, {"Date_6", "Date_3"}, {"Number_1", "Number_2"}, {"Number_4", "Number_3"}, {"Amount_5", "Amount_3"}, {"Amount", "Amount_1"}, {"Number", "Number_1"} } ), #"Added Index" = Table.AddIndexColumn(#"Renamed Columns", "Index", 1, 1, Int64.Type), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index"}, "Attribute", "Value"), #"Split Column by Delimiter" = Table.SplitColumn( #"Unpivoted Columns", "Attribute", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"Column", "ColumnGroup"} ), #"Pivoted Column" = Table.Pivot( #"Split Column by Delimiter", List.Distinct(#"Split Column by Delimiter"[Column]), "Column", "Value" ), #"Changed Type" = Table.TransformColumnTypes( #"Pivoted Column", { {"Index", Int64.Type}, {"ColumnGroup", Int64.Type}, {"Number", Int64.Type}, {"Amount", Currency.Type}, {"Date", type date} } ) in #"Changed Type"
That sound similar to my 3rd solution but I don't see where List.Zip comes into play.
Also, note that you probably want List.Combine rather than List.Union since the latter removes duplicates (assuming you don't use the equationCriteria argument).
AlexisOlson @I agree--List.Combine is the way to go. But as for List.Zip, after you List.Combine each of the 3 columns that are similar, you have 3 lists of column values. If you List.Zip the lists, you get a list of lists combined by position, so a list of the first evaluate each column than a list of the second value in each column, and so forth. Then you have the list structure for Table.FromColumns:
Table.FromColumns({
{1, "Bob", "123-4567"},
{2, "Jim", "987-6543"},
{3, "Paul", "543-7890"}
})
So
= Table.FromColumns(List.Zip({List.Combine({Table[Number], Table[Number_1], Table[Number_4]}), List.Combine({Table[Date], Table[Date_2], Table[Date_5]}), List.Combine({Table[Amount], Table[Amount_3], Table[Amount_6]})}))
--Nate