Forum Discussion

anshpalash's avatar
anshpalash
Icon for Helper II rankHelper II
4 years ago
Solved

Data Manipulation

Hi,   I have data in format:   12301 12301 896803 896803   In every odd row, before the last two digits of the number, I want to add 01. In every even row, before the last two d...
Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    4 years ago

    Hi anshpalash ,

     

    Using below M codes:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNjBUitVBZllYmlkYGKMwYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [text = _t]),
    #"Duplicated Column" = Table.DuplicateColumn(Source, "text", "text - Copy"),
    #"Changed Type" = Table.TransformColumnTypes(#"Duplicated Column",{{"text", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each Number.IsOdd([Index])),
    #"Split Column by Position" = Table.SplitColumn(#"Added Custom", "text", Splitter.SplitTextByPositions({0, 2}, true), {"text.1", "text.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"text.1", type text}, {"text.2", type text}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type1", "Custom.1", each if [Custom]=true then Text.Combine({[text.1],"01",[text.2]})
else Text.Combine({[text.1],"02",[text.2]})),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"text.1", "text.2", "Index", "Custom"})
in
    #"Removed Columns"

    And you will see:

    For the related .pbix file,pls see attached.

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my reply as a solution!

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

Since there isn't an inherent concept of row number, I will assume that you have an index column that allows you to determine odd/even.

 

You can do this in DAX as follows:

Output =
     10000 * INT ( Data[Col1] / 100 ) +
     100 * ( MOD ( Data[Index] + 1, 2 ) + 1 ) +
     MOD ( Data[Col1], 100 )

 

You could do this with a custom column in M too just changing the function names.

anshpalash's avatar
anshpalash
Icon for Helper II rankHelper II
4 years ago

INT and MOD is not defined. What is that? How is this code adding 01 for evering odd row and 02 for every even row at the specified position?

  • AlexisOlson's avatar
    AlexisOlson
    Icon for Super User rankSuper User
    4 years ago

    INT and MOD are definitely defined in DAX. Are you trying to do this in the query editor instead?

     

    The explanation is clearest with an example.

     

    1000 * INT ( 896803 / 100 )
     = 1000 * INT ( 8968.03 )
     = 1000 * 8968
     = 89680000

100 * ( MOD ( 3 + 1, 2 ) + 1 )
    = 100 * ( MOD ( 4, 2 ) + 1 )
    = 100 * ( 0 + 1 )
    = 100

MOD ( 896803 , 100 ) = 3

89680000
+    100
+      3
========
89680103