Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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"
Proud to be a Super User!
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).
Proud to be a Super User!
Please find the currently available format and the expected format
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:
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"
Proud to be a Super User!
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"
Proud to be a Super User!
User | Count |
---|---|
98 | |
90 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
67 |