Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
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!
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!
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.