Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
100 | |
99 | |
80 | |
77 | |
66 |
User | Count |
---|---|
134 | |
108 | |
104 | |
83 | |
73 |