The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi,
I'm going crazy, I'm trying to add a new column in Powerquery that works similar to Excel as Summewenns. The important thing is, it should work without
grouping.
My table, looks like this
Article number | Some other colums x1-x33 | Invoice No. | Item number-returns | Invoice number-returns | Turnoner | NEW Column |
1 | 20 | 1 | 20 | 100 | 350 | |
2 | 30 | 2 | 30 | 150 | 150 | |
3 | 40 | 3 | 30 | 200 | 0 | |
1 | 20 | 2 | 30 | 250 | 150 |
The column Turnoner is to be added Criteria area 1 is the column Article number with the creterium which stands in Item number-returns, the second riterien area stands in Invoice No. whereby the creterium stands in Invoice number-returns.
Thanks
Solved! Go to Solution.
=let a=Table.Buffer(Table.Group(PreviousStepName,{"Article number","Invoice No."},{"n",each List.Sum([Turnover])})) in Table.AddColumn(PreviousStepName,"NewColumn",each a{[#"Article number"=[#"Item number-returns"],#"Invoice No."=[#"Invoice number-returns"]]}?[n]? ??0)
=let a=Table.Buffer(Table.Group(PreviousStepName,{"Article number","Invoice No."},{"n",each List.Sum([Turnover])})) in Table.AddColumn(PreviousStepName,"NewColumn",each a{[#"Article number"=[#"Item number-returns"],#"Invoice No."=[#"Invoice number-returns"]]}?[n]? ??0)
This is only for own if, but I need it for two ifs.
Hi, that's it:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUVIAYiMDIGEIZxiASGNTA6VYnWglI6gaY5CgEYxhaAojQWqMoWpMwBrhisHmQFSg2AQ3xQhhSiwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Article number" = _t, #"Some other colums x1-x33" = _t, #"Invoice No." = _t, #"Item number-returns" = _t, #"Invoice number-returns" = _t, Turnoner = _t, #"NEW Column" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Turnoner", type number}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Article number"}, #"Changed Type", {"Article number"}, "Source", JoinKind.LeftOuter),
#"Aggregated {0}" = Table.AggregateTableColumn(#"Merged Queries", "Source", {{"Turnoner", List.Sum, "Sum Turnoner"}})
in
#"Aggregated {0}"