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.
Hello,
This is my original table:
COLUMN A | COLUMN B |
Car | 100 |
Car | 100 |
Bike | 80 |
Bike | 80 |
Bike | 80 |
Skate | 45 |
Laptop | 100 |
Phone | 20 |
The result:
Column A | Column B | Column C |
Car | 100 | 100 |
Car | 100 | 0 |
Bike | 80 | 80 |
Bike | 80 | 0 |
Bike | 80 | 0 |
Skate | 45 | 45 |
Laptop | 100 | 100 |
Phone | 20 | 20 |
The new Column C will get values from Column B based on distinct values from Column A.
Thank you in advance.
Best Regards,
Farid.
Solved! Go to Solution.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wck4sUtJRMjQwUIrVQec5ZWanArkWhHnB2YklIK6JKZjrk1hQkl+AZFJARn4eSN4IyI0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"COLUMN A" = _t, #"COLUMN B" = _t]),
#"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "COLUMN C", each if [Index]>0 and [COLUMN A]=#"Added Index"{[Index]-1}[COLUMN A] then 0 else [COLUMN B]),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"COLUMN A", "COLUMN B", "COLUMN C"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"COLUMN C", type number}})
in
#"Changed Type"
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wck4sUtJRMjQwUIrVQec5ZWanArkWhHnB2YklIK6JKZjrk1hQkl+AZFJARn4eSN4IyI0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"COLUMN A" = _t, #"COLUMN B" = _t]),
#"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "COLUMN C", each if [Index]>0 and [COLUMN A]=#"Added Index"{[Index]-1}[COLUMN A] then 0 else [COLUMN B]),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"COLUMN A", "COLUMN B", "COLUMN C"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"COLUMN C", type number}})
in
#"Changed Type"
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.
User | Count |
---|---|
112 | |
100 | |
77 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |