Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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 colum...
Need Help
  • Jimmy801's avatar
    Jimmy801
    5 years ago

    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
    CombineText

     

    Copy 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

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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.

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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

  • Jimmy801's avatar
    Jimmy801
    Community Champion
    5 years ago

    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
    CombineText

     

    Copy 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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    the solution I was thinking of is the one indicated by Jimmy801 .

    if you need a more specific example on a sample of your data, you have to upload the file somehow