Moving random cells to another column

[Eqa33]

I have a column in power query showing a description ABC and listed below is a stock ID number 123, then 30 cells later EFG and 456 appears 50 cells later HIJ followed by a random amount of 789 until the next change.
How do I get a new column showing the Descriptions (ABC) etc and the numeric numbers remaining in the original column?

Thanks for your help.

Eqa

 

[alansidman]

Cannot manipulate data in a picture. Please load your workbook using XL2BB. Also, your explanation bears no resemblance to the picture. Please adjust your explanation to relate to your file.

 

[GraH]

Add a custom column with an if statement that checks if column 2 is empty. If so return column1 else null.
Fill down. Filter out where column2 is null.
If needed move new column to first position in the table.

 

[Eqa33]

Add a custom column with an if statement that checks if column 2 is empty. If so return column1 else null.
Fill down. Filter out where column2 is null.
If needed move new column to first position in the table.

Thanks for your answer.

I understand making a copy of the column but if I only want the description which in the example I posted is Paisley Floral and then fill down till the next change in description say XXXX, how do I achieve that? So the first column will have all the numeric style numbers SL3073PF and the second column will have the style descriptions Paisley Floral.

Thanks for your assistance.

 

[GraH]

Custom column is not a copy column. Try to follow my suggestion with the if statement inside it.
If you could share a sample data set (see comment #2 by Allan)... Will make it easier for us as well.

 

[GraH]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"stylenumber", type text}, {"garment", type text}, {"colour", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "style", each if [garment]=null then [stylenumber] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"style"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Filled Down",{"style", "stylenumber", "garment", "colour"}),
    #"Filtered Rows" = Table.SelectRows(#"Reordered Columns", each ([garment] <> null))
in
    #"Filtered Rows"

 

[Eqa33]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"stylenumber", type text}, {"garment", type text}, {"colour", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "style", each if [garment]=null then [stylenumber] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"style"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Filled Down",{"style", "stylenumber", "garment", "colour"}),
    #"Filtered Rows" = Table.SelectRows(#"Reordered Columns", each ([garment] <> null))
in
    #"Filtered Rows"

View attachment 53008

That's exactly what I want to achieve.
Being very new to power query what exact steps do I need to take to achieve this in Power Query? I know this may be frustrating but appreciate your help.

 

[alansidman]

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

 

[GraH]

That's exactly what I want to achieve.
Being very new to power query what exact steps do I need to take to achieve this in Power Query? I know this may be frustrating but appreciate your help.

It is actually explained in #3.

1. Add a custom column
2. Take the if statement from from #6
3. Fill down that new column
4. Filter out where garment is null (much like in Excel table or range)
5. Select the new column and move to first position. Can be done with a mouse right click after selecting the column.
6. Save and load to excel.

All what Allan said in #8 is solid advice I'll second.

 

[Eqa33]

It is actually explained in #3.

1. Add a custom column
2. Take the if statement from from #6
3. Fill down that new column
4. Filter out where garment is null (much like in Excel table or range)
5. Select the new column and move to first position. Can be done with a mouse right click after selecting the column.
6. Save and load to excel.

All what Allan said in #8 is solid advice I'll second.

Thanks very much. I'm just starting to get me head around Power Query and to understand the enormous power and flexibility it offers.

Thanks again.

Eqa