Forum Discussion
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?
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,
KellyDid I answer your question? Mark my reply as a solution!
4 Replies
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
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,
KellyDid I answer your question? Mark my reply as a solution!
