Forum Discussion

lucasmnx's avatar
lucasmnx
Frequent Visitor
4 years ago
Solved

Split row interval into colmun content

Hello,   I have a file with a column labels with a interval and months and I want to transform this content into content to the column 2, so in the column 2 I will have MTR STATIC repeated on side ...
Need Help
  • MFelix's avatar
    MFelix
    4 years ago

    Hi lucasmnx ,

     

    Format column 2 to text then do a replace values, does not matter what you put on that replace you will get a formula similar to this one:

    = Table.ReplaceValue(#"Changed Type","a","aa",Replacer.ReplaceText,{"Column2"})

     

    Now replace the value of this step by the following formula:

    = Table.ReplaceValue(#"Changed Type", each [Column2] , each if Text.StartsWith ([#"In K HK$"] , "MTR") then [#"In K HK$"] else null,Replacer.ReplaceValue,{"Column2"})

    Do a fill down:

     

    Now you just need to filter out the rows that do not start with MTR:

     

     

     

     

     

MFelix's avatar
MFelix
Icon for Super User rankSuper User
4 years ago

Hi lucasmnx ,

 

Format column 2 to text then do a replace values, does not matter what you put on that replace you will get a formula similar to this one:

= Table.ReplaceValue(#"Changed Type","a","aa",Replacer.ReplaceText,{"Column2"})

 

Now replace the value of this step by the following formula:

= Table.ReplaceValue(#"Changed Type", each [Column2] , each if Text.StartsWith ([#"In K HK$"] , "MTR") then [#"In K HK$"] else null,Replacer.ReplaceValue,{"Column2"})

Do a fill down:

 

Now you just need to filter out the rows that do not start with MTR: