Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello everyone,
I have another request please.
I have managed to get what I want but I find that my code has too many steps.
Can I get the same result with only 2 or 3 steps?
Thank you in advance 😊.
Regards
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Order", Int64.Type}, {"Poste", Int64.Type}, {"Amount", Int64.Type}}),
#"Added Custom" = Table.Group(#"Changed Type",{"Order"},{{"Sum", each List.Sum([Amount]), type nullable number}, {"Detail", each _, type table [Order=nullable number, Poste=nullable number, Amount=nullable number]}}),
#"Expanded Detail" = Table.ExpandTableColumn(#"Added Custom", "Detail", {"Poste", "Amount"}, {"Poste", "Amount"}),
#"Added Index" = Table.AddIndexColumn(#"Expanded Detail", "Index", 0, 1, Int64.Type),
#"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 1, 1, Int64.Type),
#"Merged Queries" = Table.NestedJoin(#"Added Index1", {"Index.1"}, #"Added Index1", {"Index"}, "Added Index1", JoinKind.LeftOuter),
#"Expanded Added Index1" = Table.ExpandTableColumn(#"Merged Queries", "Added Index1", {"Order"}, {"Order.1"}),
#"Added Custom1" = Table.AddColumn(#"Expanded Added Index1", "Custom", each if [Order] <> [Order.1] then [Index.1] else null),
#"Added Conditional Column" = Table.AddColumn(#"Added Custom1", "Total", each if [Order.1] = [Order] then null else [Sum]),
#"Removed Other Columns" = Table.SelectColumns(#"Added Conditional Column",{"Order", "Poste", "Amount", "Total"})
in
#"Removed Other Columns"
Order Poste Amount
8900012 | 1 | 35 |
8900019 | 1 | 22 |
8900019 | 2 | -44 |
8900019 | 3 | 13 |
8900025 | 2 | 7 |
8900025 | 3 | 15 |
8900017 | 1 | 43 |
8900017 | 3 | -8 |
8900017 | 4 | 16 |
8900017 | 5 | 4 |
Solved! Go to Solution.
Hello - this is how you can get the same result in less steps:
SCRIPT:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Xc1LCsAgDEXRvWSsEPOpuhZx/9toIyktb5DJyYW3Fo3JzE2oUHtOnXZ5cSaKAEZdzUA1cv1QPNMOdsL/UM8hU8Ao6wC0qC9AP4+9bw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Order = _t, Poste = _t, Amount = _t]),
ChangeType = Table.TransformColumnTypes(Source,{{"Order", Int64.Type}, {"Poste", Int64.Type}, {"Amount", Int64.Type}}),
Custom = Table.AddColumn ( ChangeType, "Custom", each
let
t = Table.SelectRows(ChangeType, (x)=> x[Order]=[Order]),
varSum = List.Sum ( t[Amount] ),
varMax = List.Max ( t[Poste] )
in
if [Poste] = varMax then varSum else null
)
in
Custom
RESULT:
Hello everyone, hello @jennratten ,
Sorry, I tried to integrate 2 more columns, Index and Table.Buffer but it still takes a long time to update.
Maybe I didn't complete the code properly ?
How can I speed up the code for method 2 ?
Thanks in advance
Regards
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
ChangeType = Table.TransformColumnTypes(Source,{{"Order", Int64.Type}, {"Poste", Int64.Type}, {"Amount", Int64.Type}}),
#"Added Index" = Table.AddIndexColumn(ChangeType, "Index", 1, 1, Int64.Type),
#"Sorted Rows" = Table.Buffer(Table.Sort(#"Added Index",{{"Index", Order.Ascending}})),
Custom = Table.AddColumn ( #"Sorted Rows", "Custom", each
let
t = Table.SelectRows(#"Sorted Rows", (x)=> x[Order]=[Order]),
varSum = List.Sum ( t[Amount] ),
varMax = List.Max ( t[Poste] )
in
if [Poste] = varMax then varSum else null
)
in
Custom
I just thought of adding an Index column before your step.
I sorted the Index column and put it under Table.Buffer()
This solves the slowness problem, it takes less than 10 seconds and your code does the job very well😊,
Sorry for the inconvenience, but if there is an easier way, I'll take it
Thanks again
Regards
Sorry, I tried your code on my file of 2000 rows and about ten columns, it takes very long time.
I give up after 15 min and I don't know how to place List.Buffer() in this code
Maybe this would help to reduce the time considerably !!
I've done several tests but with no result
Thanks in advance
Regards
Hello - this is how you can get the same result in less steps:
SCRIPT:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Xc1LCsAgDEXRvWSsEPOpuhZx/9toIyktb5DJyYW3Fo3JzE2oUHtOnXZ5cSaKAEZdzUA1cv1QPNMOdsL/UM8hU8Ao6wC0qC9AP4+9bw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Order = _t, Poste = _t, Amount = _t]),
ChangeType = Table.TransformColumnTypes(Source,{{"Order", Int64.Type}, {"Poste", Int64.Type}, {"Amount", Int64.Type}}),
Custom = Table.AddColumn ( ChangeType, "Custom", each
let
t = Table.SelectRows(ChangeType, (x)=> x[Order]=[Order]),
varSum = List.Sum ( t[Amount] ),
varMax = List.Max ( t[Poste] )
in
if [Poste] = varMax then varSum else null
)
in
Custom
RESULT:
Wonderful, thank you very much, you have all helped me very well,
I think that's enough for today 😊
Have a nice evening to all,
Regards
You're welcome!