Forum Discussion
Merge all available Columns Power Query
Hi All
This is Probably a simple one for anyone who understand M Language..
I'm pulling text from a PDF and need to know how i Merge all columns into 1 in Power Query when the number of columns chnages each refresh.. i've built the below query in power query (my M language is not the best) which works however if i refresh the query and the number of columns in the data set has change it will error. so what i want instead is the query to merge all the available columns.
example below i have 23 columns which i can merge and combine all the text into 1 cell in 1 column but next time i refresh i may only i may have 26 columns or only 8 columns so the query will errorBefore Mergewhat I need as the end product
This is my M Query
let
Source = Pdf.Tables(File.Contents("C:\Users\Jimmy.Smith\Downloads\Russia.pdf"), [Implementation="1.1"]),
Page1 = Source{[Id="Page001"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Page1,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type number}, {"Column6", type number}, {"Column7", type number}, {"Column8", type number}, {"Column9", type number}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Column5] = null)),
#"Removed Bottom Rows" = Table.RemoveLastN(#"Filtered Rows",3),
#"Filtered Rows1" = Table.SelectRows(#"Removed Bottom Rows", each ([Column4] <> null)),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows1",{"Column1", "Column2", "Column3", "Column5", "Column6", "Column7", "Column8", "Column9"}),
#"Added Index" = Table.AddIndexColumn(#"Removed Columns", "Index", 1, 1, Int64.Type),
#"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"Index", "Column4"}),
#"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Column4", "Report"}}),
#"Transposed Table" = Table.Transpose(#"Renamed Columns"),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Transposed Table", {{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}, {"Column14", type text}, {"Column15", type text}, {"Column16", type text}, {"Column17", type text}, {"Column18", type text}, {"Column19", type text}, {"Column20", type text}, {"Column21", type text}, {"Column22", type text}, {"Column23", type text}}, "en-GB"),{"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged")
in
#"Merged Columns"
Hello Anonymous
use Text.Combine and refer to your report-column. Heren an example
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJ0UorViVYyAjKdXcBMYyDT1Q3MNAEy3T30wGxTINtTKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Index = _t, Report = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"Report", type text}}), CombineText = Text.Combine(#"Changed Type"[Report], " ") in CombineTextCopy paste this code to the advanced editor in a new blank query to see how the solution works.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
5 Replies
In general if you have to apply a function to a dinamically changing set of columns, you should get a list of column in a variable, as, f.i., cols=Table.ColumnNames(yourtab), then use this variable-list intead of hard coded list of column names: f.i. Table.CombineColumns(youratab, cols, combinerFunction, "colCombined").
But in this specific case I wonder if it is necessary to proceed in this way, considering that you have a column of textual values you transpose to have them in the same row in different columns.
I think it is easier to act on the original column.
but I could confirm this if you made available an easily copyable source file and maybe you explained better what you want to achieve.
Thanks Rocco
when i pull the data from the PDF i end up with multiple rows of text in 1 column (this is what i'm transposing to columns to then combine as i wasn't sure of a way to merge rows?). my end goal is combine of all of the text into a single cell with a space in between each combination. Basically all of the individual rows of text should combined to make a readable paragraph of text in 1 cell.
I'm unsure of how to attached a PDF file on here but below is how the text reads before i transpose, as you can see its all in seperate rows. the Index i added just i could keep the correct order, but if it was all combined in 1 cell the index would not be needed.
hopefully this better explains what i am trying to doon here
Hello Anonymous
use Text.Combine and refer to your report-column. Heren an example
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJ0UorViVYyAjKdXcBMYyDT1Q3MNAEy3T30wGxTINtTKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Index = _t, Report = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"Report", type text}}), CombineText = Text.Combine(#"Changed Type"[Report], " ") in CombineTextCopy paste this code to the advanced editor in a new blank query to see how the solution works.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy