Add Column data to the bottom of another column

[LAHIRU92]

In my requirement,I want to make a single column from the other columns, I need to move the data in other columns to the bottom of the first column,

Ex: My table have 8 columns with 50 rows,I want to covert it to the 1 column with 400 rows

How can I do this task

[danextian]

Hi @LAHIRU92 ,

I have a vague idea of what you want to achieve. This can probably be done in Power Query. Can you please post a sample data and your expected result (please do not post an image but a link to an Excel file or text that we anyone can easily copy-paste).

[LAHIRU92]

https://docs.google.com/spreadsheets/d/1j_tv0_mab4wAhoZNsI6B0nJSYoWf0NAP/edit?usp=sharing&ouid=11053... 

Please find the currently available format and the expected format

[danextian]

Hi @LAHIRU92 

This is a sample M code based on the attached sample data. The code dynamically appends each column to the other regardlesss of the number of columsn. Replace the following with whatever is applicable:

• C:\Users\uer\Downloads\Sample.xlsx
• Data_Available Type -  this is the sheet name of your data

let
    Source = Excel.Workbook(File.Contents("C:\Users\221725.Dbelarmino\Downloads\Sample.xlsx"), null, true),
    #"Data_Available Type_Sheet" = Source{[Item="Data_Available Type",Kind="Sheet"]}[Data],
    Custom1 = List.Combine( List.Transform(Table.ColumnNames(#"Data_Available Type_Sheet"), (x) =>  Table.ToList(Table.SelectColumns(#"Data_Available Type_Sheet", x)))),
    #"Converted to Table" = Table.FromList(Custom1, Splitter.SplitByNothing(), {"Combined"}, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Combined", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Combined] <> null and [Combined] <> "")
in
    #"Filtered Rows"

[danextian · Accepted solution]

Another approach would be, a much simpler on, to create create a custom column and input any value or null, elect this custom column, right click and select unpivot other columns.

Here is a sample code

let
    Source = Excel.Workbook(File.Contents("C:\Users\221725.Dbelarmino\Downloads\Sample.xlsx"), null, true),
    #"Data_Available Type_Sheet" = Source{[Item="Data_Available Type",Kind="Sheet"]}[Data],
    #"Added Custom" = Table.AddColumn(#"Data_Available Type_Sheet", "Herlper Column", each null),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Custom", {"Herlper Column"}, "Attribute", "Value"),
    #"Removed Other Columns" = Table.SelectColumns(#"Unpivoted Other Columns",{"Value"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each [Value] <> null and [Value] <> ""),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Value", "Combined Columns"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Combined Columns", type text}})
in
    #"Changed Type"