Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors