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.
Good day,
Please could someone tell me if it is possible to Index columns. I need two columns, one Index column starting at 0 and the other starting at 1. The only issue is it needs to be grouped by Client no.
Please see below example.
ClientNo | Date | Ice Creams | Rate | Index | Index From 0 |
1234 | 2019/12/01 | 20000 | 10 | 1 | 0 |
1234 | 2019/12/01 | 1000 | 10 | 2 | 1 |
1234 | 2019/12/01 | 5000 | 11 | 3 | 2 |
1234 | 2019/12/02 | 2000 | 12 | 4 | 3 |
1234 | 2019/12/02 | 9000 | 11 | 5 | 4 |
1234 | 2019/12/02 | 5000 | 13 | 6 | 5 |
1234 | 2019/12/03 | 6000 | 14 | 7 | 6 |
1234 | 2019/12/04 | 6000 | 12 | 8 | 7 |
1234 | 2019/12/05 | 5000 | 11 | 9 | 8 |
1258 | 2019/12/05 | 2000 | 20 | 1 | 0 |
1258 | 2019/12/05 | 1000 | 10 | 2 | 1 |
1258 | 2019/12/05 | 6000 | 15 | 3 | 2 |
1258 | 2019/12/05 | 5000 | 12 | 4 | 3 |
1258 | 2019/12/06 | 1000 | 13 | 5 | 4 |
1258 | 2019/12/06 | 4000 | 14 | 6 | 5 |
1258 | 2019/12/06 | 5000 | 15 | 7 | 6 |
1258 | 2019/12/06 | 6000 | 20 | 8 | 7 |
1258 | 2019/12/06 | 4000 | 10 | 9 | 8 |
1258 | 2019/12/06 | 500 | 11 | 10 | 9 |
Thanks in advance !
Regards,
Solved! Go to Solution.
Hello @Anonymous,
you have to Group the data and then apply TransformColumns twice
Here the solution for you
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hdBRDsAQDAbgu3iW0GplziLuf43Zwma040UkX/x/m7MBDGSsQQ/JAToP96OeeoM3xcoEtoKbAFFgi7kEqiJt/+gpQRShPmITJAoahNyDhVn4mEWbBb0q5o2tovdgVfDU9CvikBJUQdM+VsG/PeLbVJl2TNEFPystJw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ClientNo = _t, Date = _t, #"Ice Creams" = _t, Rate = _t]),
#"Changed Type" = Table.TransformColumnTypes
(
Source,
{{"ClientNo", Int64.Type}, {"Date", type date}, {"Ice Creams", Int64.Type}, {"Rate", Int64.Type}}
),
#"Grouped Rows" = Table.Group
(
#"Changed Type",
{"ClientNo"},
{{"AllRows", each _, type table [ClientNo=number, Date=date, Ice Creams=number, Rate=number]}}
),
AddIndex0 = Table.TransformColumns
(
#"Grouped Rows",
{{"AllRows", each Table.AddIndexColumn
(
_,
"Index0",
0
)}}
),
AddIndex1 = Table.TransformColumns
(
AddIndex0,
{{"AllRows", each Table.AddIndexColumn
(
_,
"Index1",
1
)}}
),
#"Expanded AllRows" = Table.ExpandTableColumn(AddIndex1, "AllRows", {"Date", "Ice Creams", "Rate", "Index0", "Index1"}, {"Date", "Ice Creams", "Rate", "Index0", "Index1"})
in
#"Expanded AllRows"
If this post helps or solves your problem, please mark it as solution.
Kudos are nice to - thanks
Have fun
Jimmy
Hello @Anonymous
any news?
Jimmy
Hello @Anonymous,
you have to Group the data and then apply TransformColumns twice
Here the solution for you
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hdBRDsAQDAbgu3iW0GplziLuf43Zwma040UkX/x/m7MBDGSsQQ/JAToP96OeeoM3xcoEtoKbAFFgi7kEqiJt/+gpQRShPmITJAoahNyDhVn4mEWbBb0q5o2tovdgVfDU9CvikBJUQdM+VsG/PeLbVJl2TNEFPystJw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ClientNo = _t, Date = _t, #"Ice Creams" = _t, Rate = _t]),
#"Changed Type" = Table.TransformColumnTypes
(
Source,
{{"ClientNo", Int64.Type}, {"Date", type date}, {"Ice Creams", Int64.Type}, {"Rate", Int64.Type}}
),
#"Grouped Rows" = Table.Group
(
#"Changed Type",
{"ClientNo"},
{{"AllRows", each _, type table [ClientNo=number, Date=date, Ice Creams=number, Rate=number]}}
),
AddIndex0 = Table.TransformColumns
(
#"Grouped Rows",
{{"AllRows", each Table.AddIndexColumn
(
_,
"Index0",
0
)}}
),
AddIndex1 = Table.TransformColumns
(
AddIndex0,
{{"AllRows", each Table.AddIndexColumn
(
_,
"Index1",
1
)}}
),
#"Expanded AllRows" = Table.ExpandTableColumn(AddIndex1, "AllRows", {"Date", "Ice Creams", "Rate", "Index0", "Index1"}, {"Date", "Ice Creams", "Rate", "Index0", "Index1"})
in
#"Expanded AllRows"
If this post helps or solves your problem, please mark it as solution.
Kudos are nice to - thanks
Have fun
Jimmy
First you want to do a "group by", with "All rows" as your operation, and you can give the column a name. e.g. "group"
Then add a custom column with the formula:
= Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([group],"Index"))
Next you want to expand the index column of the new table
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.