none
Power Bi Extract a value RRS feed

  • Question

  • Hi,

    i would like to know how can i create a column which extract the value from another column.

    On this example i would like to extract the number after "ATRAQ-", so if in the column there is ATRAQ-10 i want to extract the value 10 and same if there is "ATRAQ-12,ATRAQ-13" i want the two value.

    Thanks

    Friday, May 10, 2019 8:05 AM

Answers

  • let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        AddCol = Table.AddColumn(Source, "value list", each
            try
                List.ReplaceValue(
                    List.Select(
                        Text.Split(Text.From([value]), ","),
                        each Text.StartsWith(Text.Trim(_),"ATRAQ-")
                    ),
                    "ATRAQ-", 
                    "",
                    Replacer.ReplaceText
                )
            otherwise {}
        ),
    
        ExtractValues = Table.TransformColumns(AddCol, {"value list", each Text.Combine(List.Transform(_, Text.From), ","), type text})
    in ExtractValues


    Friday, May 10, 2019 10:43 AM
  • Hi LitSin,

    if you create the column by clicking the symbol "Custom Column" in the General-section of the "Add Colum"-tab,

    you should just paste this part of Anthony's the code into the dialogue box:

    try
                List.ReplaceValue(
                    List.Select(
                        Text.Split(Text.From([value]), ","),
                        each Text.StartsWith(Text.Trim(_),"ATRAQ-")
                    ),
                    "ATRAQ-", 
                    "",
                    Replacer.ReplaceText
                )
            otherwise {}


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Sunday, May 26, 2019 5:01 AM
    Moderator

All replies

  • let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        AddCol = Table.AddColumn(Source, "value list", each
            try
                List.ReplaceValue(
                    List.Select(
                        Text.Split(Text.From([value]), ","),
                        each Text.StartsWith(Text.Trim(_),"ATRAQ-")
                    ),
                    "ATRAQ-", 
                    "",
                    Replacer.ReplaceText
                )
            otherwise {}
        ),
    
        ExtractValues = Table.TransformColumns(AddCol, {"value list", each Text.Combine(List.Transform(_, Text.From), ","), type text})
    in ExtractValues


    Friday, May 10, 2019 10:43 AM
  • @anthony34

    it doesn't work, when i write this to create a column 

    let
    Col=Table.AddColumn(Source,"value", each
    try
                List.ReplaceValue(
                    List.Select(
                        Text.Split(Text.From([value]), ","),
                        each Text.StartsWith(Text.Trim(_),"ATRAQ-")
                    ),
                    "ATRAQ-", 
                    "",
                    Replacer.ReplaceText
                )
            otherwise {}
        ),

        ExtractValues = Table.TransformColumns(Col, {"value", each Text.Combine(List.Transform(_, Text.From), ","), type text})
    in ExtractValues

    It's creating a table 

    But there is not the column i wanted


    • Edited by LitSin Friday, May 10, 2019 11:54 AM
    Friday, May 10, 2019 11:52 AM
  • you already have a column called "value" in your original table. Then you cannot add another column with the same name. It is the reason why I called the new column "value list" in my code.

    Then in your code your are expanding your original "value" column, which cannot lead to the expected result. You need to expand the  new additional column .

    Just stick to my code, it works


    • Edited by anthony34 Saturday, May 11, 2019 5:57 PM
    Saturday, May 11, 2019 5:55 PM
  • Hi LitSin,

    if you create the column by clicking the symbol "Custom Column" in the General-section of the "Add Colum"-tab,

    you should just paste this part of Anthony's the code into the dialogue box:

    try
                List.ReplaceValue(
                    List.Select(
                        Text.Split(Text.From([value]), ","),
                        each Text.StartsWith(Text.Trim(_),"ATRAQ-")
                    ),
                    "ATRAQ-", 
                    "",
                    Replacer.ReplaceText
                )
            otherwise {}


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Sunday, May 26, 2019 5:01 AM
    Moderator