cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
anshpalash
Helper II
Helper II

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 digits of the number, I want to add 02.

 

The output should look like:

 

1230101
1230201
89680103
89680203

 

Can anyone please advise how to dynamically solve this problem?

 

1 ACCEPTED SOLUTION
v-kelly-msft
Community Support
Community Support

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:

vkellymsft_0-1635145274350.png

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

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

View solution in original post

4 REPLIES 4
v-kelly-msft
Community Support
Community Support

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:

vkellymsft_0-1635145274350.png

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

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

View solution in original post

AlexisOlson
Super User
Super User

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.

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?

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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors