Delete numbers from columns to fill down

Bernie

Bernie

Member
Joined
Feb 9, 2018
Messages
34
Reaction score
0
Points
6
Location
Suburban NY
Excel Version(s)
Excel 2016 (Win)
I have a column of data in a query that looks like

Test Setting at 90 degrees
70.34
-60.45
50.56
-40.67

and I would like to convert it to

Test Setting at 90 degrees
Test Setting at 90 degrees
Test Setting at 90 degrees
Test Setting at 90 degrees
Test Setting at 90 degrees

The only way I could figure out was to Replace "Test" with " Test" and then split the column based on position, do some other manipulations to get null values, and then use fill down - I'm hoping there is a simpler way!

Thanks so much,
Bernie Deitrick
 
Conditional column - pull across just the text that starts with test - fill down - delete original column.
 
Thanks - that works with one string, but if I have multiple string values as headers, is there a function that can identify any string compared to numbers, rather than looking as specific text?
 
Provide some sample data - attach a workbook.

You may be able to use this:

Code: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type any}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Value.Is(Value.FromText([Column1]), type number) then null else [Column1]),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
    #"Removed Columns" = Table.RemoveColumns(#"Filled Down",{"Column1"})
in
    #"Removed Columns"
 
I want to convert Table1 into Table2.
 

Attachments

  • Post example file.xlsx
    11.6 KB · Views: 13
Ali,

Your line:

Code: 
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Value.Is(Value.FromText([Column1]), type number) then null else [Column1]),

worked great, and I was able to do both of the columns as I needed.
 
My take on the situation
Code: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Custom", each if Text.StartsWith([Column2], "70") then [Column2] else null),
    #"Added Conditional Column" = Table.AddColumn(#"Added Custom", "Custom.1", each if Text.Contains([Column1], "Data") then [Column1] else if Text.Contains([Column1], "data") then [Column1] else null),
    #"Filled Down1" = Table.FillDown(#"Added Conditional Column",{"Custom.1"}),
    #"Filled Up" = Table.FillUp(#"Filled Down1",{"Custom.1"}),
    #"Filtered Rows1" = Table.SelectRows(#"Filled Up", each ([Column2] <> null)),
    #"Filtered Rows" = Table.SelectRows(#"Filtered Rows1", each ([Column2] <> null)),
    #"Filled Down" = Table.FillDown(#"Filtered Rows",{"Custom"}),
    #"Renamed Columns" = Table.RenameColumns(#"Filled Down",{{"Custom.1", "Test Component"}, {"Custom", "Test"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Test", "Test Component", "Column1", "Column2"}),
    #"Filtered Rows2" = Table.SelectRows(#"Reordered Columns", each ([Column1] <> "Current Test")),
    #"Pivoted Column" = Table.Pivot(#"Filtered Rows2", List.Distinct(#"Filtered Rows2"[Column1]), "Column1", "Column2")
in
    #"Pivoted Column"
 
That's a lot of M Code - is there a reason you decided to do it this way?
 
He's providing the full solution as per the OPs example.
 
Thanks, Bob - I was asking Alan. His wording suggested a different approach.

Alan - if Bob has answered correctly on your behalf, just ignore my question. ;)
 
Ali
I was intrigued by the question and had no original plan to post. It was for my own edification. Just decided to give it a step by step try and when it worked for me, then I decided to post. Afterall, people look at these forums for solutions and it may be useful to the masses and not just the OP. I like solving puzzles like Soduko and crosswords and look at this in a similar manner.

Alan
 
Thank you Alan - your take worked well and helped me better understand how to approach my actual data issues.

I don't know if this forum uses "reputation points" but if it did, I would be clicking for both you and AliGW.

Thanks again - I'm sure I'll be back ;)

Bernie
 
You must log in or register to reply here.
Back
Top