Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi everyone,
I want to ask a help for solving my problem
I have a table of Raw Data like this
I need to sum the value per row when the header contains "." and "Qty" It will create a new column "TOTAL"
After that, I want to calculate the GAP = QTY PO CS - TOTAL
My expectation table
What M Code should I write to get my expectations?
Please help me to solve this problem
Thank you..
If the columns are fixed then just highlight the columns you want to sum and click add column > sum. Then you can click the two other columns and add column > subtract.
If your columns are *not* fixed then your table is not organized properly.
Hello, @Mars3442
let
Source = your_table,
dots = List.Buffer(List.Select(Table.ColumnNames(Source), each Text.Contains(_, "."))),
total = Table.AddColumn(
Source, "TOTAL",
each List.Sum(
Record.FieldValues(
Record.SelectFields(_, dots)
)
)
),
gap = Table.AddColumn(total, "GAP", each [QTY PO CS] - [TOTAL])
in
gap
Hi thank you for your solution but I get an error
I modify your solution like this
List = List.Buffer(List.Select(Table.ColumnNames(#"Previous Step"), each Text.Contains(_, ".")and Text.Contains(_, "Qty CS"))),
Total = Table.AddColumn( List, "Total Reason QTY CS", each List.Sum(Record.FieldValues(Record.SelectFields(_, List))))
but I got an error
Expression.Error: We cannot convert a value of type List to type Table.
Details:
Value=[List]
Type=[Type]
what should I do?