How I can imoprt data from one excel sheet to other-CodePudding

In my excel I have main sheet, that contains all the data that I need, but this data is not filtered(majority of the rows are empty), so to choose the values from particular column I need to uncheck 'Blanks' in filters and only then I can copy the data and paste it to another sheet(which should contain the data from each column). I do this for each column in my main sheet. Is there any way to import data(rows which have values values) from each column to another sheet? Because when I am doing it manually it takes a lot of time. Thx for help!

enter image description here

CodePudding user response：

If I rightly got your idea, you need such an expression:

=Sheet1!A26 Sheet1!B26

CodePudding user response：

highlight source data and bring into powerquery with data .. from table/range

add column, index column

right click index column, unpivot other columns This step auto-removes null cells

right click index column and remove

transform ... transpose

home .. use first rows as headers

file .. close and load back to excel

sample code:

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index"}, "Attribute", "Value"),
#"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Index"}),
#"Transposed Table" = Table.Transpose(#"Removed Columns"),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true])
in #"Promoted Headers"