Results 1 to 3 of 3

Thread: Auto-Fill in new column with if clause

  1. #1
    Neophyte Easd's Avatar
    Join Date
    Sep 2019
    Posts
    2
    Articles
    0
    Excel Version
    2016

    Auto-Fill in new column with if clause



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Dear forum,

    Iīve been working with power query for some weeks now, but I am struggling with a problem mentioned below.

    Below is an excerpt from a csv-file that I import with power query. The columnīs format is text, but it also contains strings that consists only of numbers (i.e. 08800,10000,11200,...). I want to add another column that adds these strings to the different rows. As soon as there appears the next row with numbers, I want that to be added. The additional rows between those
    "number-cells" vary from month to month.

    I tried some kind of if... then... clause, but I was only able to identify the cells with solo numbers... any hints? (If you have a better solution than "if... then...", of course I would be glad if you share it with me).

    Kind regards

    Click image for larger version. 

Name:	csv.JPG 
Views:	20 
Size:	21.4 KB 
ID:	9363

  2. #2
    Acolyte cyborgski's Avatar
    Join Date
    May 2019
    Posts
    54
    Articles
    0
    Excel Version
    Office 365
    Not sure if I completely understand your objective. But I think your if then else is probably fine. You just need to fill down after the add column.

    Code:
    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}}),
        #"Add id" = Table.AddColumn(#"Changed Type", "id", each if (try Number.FromText([ID]) otherwise null) = null /* Your number test might be different */ then null else [ID], type text  ),
        #"Filled Down" = Table.FillDown(#"Add id",{"id"})
    in
        #"Filled Down"

  3. #3
    Neophyte Easd's Avatar
    Join Date
    Sep 2019
    Posts
    2
    Articles
    0
    Excel Version
    2016
    Thank you very much. With so additional "Trial and Error" I was able to finish that task!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •