Forum Discussion
Transforming a messy text export
Oh, you are trying to combine the protocol and department lines into a single row. Instead of using Table.Combine, use
Table.FromColumns({#"Added Custom"[Custom], #"Filtered Rows2"[Column2]})
The original code above gave me issues trying to split the text file; there's no good delimiter I can see. SO I tried this below, both with and without the duplicate/rename column steps.
But this gives me rows that JUST start with "PROTOCOL ID" at the end. It's not returning the value from 2 rows down. I can see the other rows that start with DEPARTMENT when I clock on the #Filtered Rows2 step, but the merged query step ends up just showing me the same lines I see in the #FilteredRows1 step. Both columns remain the same in all steps.
I have isolated the two columns that I want and can see them in different steps, but just need one to appear in Column1 and the other to appear in Column2.
I feel like I'm really close!!
let Source = Table.FromColumns({Lines.FromBinary(File.Contents("C:\[PATH], null, null, 1252)}), #"Duplicated Column" = Table.DuplicateColumn(Source, "Column1", "Column1 - Copy"),
#"Renamed Columns" = Table.RenameColumns(#"Duplicated Column",{{"Column1 - Copy", "Column2"}}), #"Filtered Rows1" = Table.SelectRows(#"Renamed Columns", each Text.StartsWith([Column1], "PROTOCOL")),
#"Added Custom" = Table.AddColumn(#"Filtered Rows1", "Custom", each try #"Renamed Columns"{[Index]+2}[Column1] otherwise null),
#"Filtered Rows2" = Table.SelectRows(#"Renamed Columns", each Text.StartsWith([Column2], "DEPARTMENT")),
#"Merged Queries" = Table.Combine({#"Added Custom", #"Filtered Rows2"})
in
#"Merged Queries"
Oh, you are trying to combine the protocol and department lines into a single row. Instead of using Table.Combine, use
Table.FromColumns({#"Added Custom"[Custom], #"Filtered Rows2"[Column2]})
Yes!!! Thank you!!