If Column header start with text "" then add values under the column header for each row in M Query

[smallfires0628]

I created a pivot table in Query Editor using Pivot Column. I have financial accounts as columns and I have a project label for each row. What I want to do is create two new columns: one for Revenue and another for Cost of Revenue for each project. I know that financial accounts starting with "4" are Revenue Accounts and financial accounts starting with "5" are Costs accounts. Is there an if statement in M Query that computes values based on these conditions? 

[Vijay_A_Verma · Accepted solution]

See the code below

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRS0lEyNDAAkmbmYCaIsICzQIQRlBWrE63k7AIWAImYmoGETeHqTVE0gdTHAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Project Name" = _t, #"40567-service income" = _t, #"40785 - product income" = _t, #"40037 - additional income" = _t, #"50678 - materials cost" = _t, #"54678 - service cost" = _t, #"56890 - driver fee" = _t, #"56768 - gasoline fee" = _t, #"56780 - additional fee" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project Name", type text}, {"40567-service income", Int64.Type}, {"40785 - product income", Int64.Type}, {"40037 - additional income", Int64.Type}, {"50678 - materials cost", Int64.Type}, {"54678 - service cost", Int64.Type}, {"56890 - driver fee", Int64.Type}, {"56768 - gasoline fee", Int64.Type}, {"56780 - additional fee", Int64.Type}}),
    ListOfColumns = List.Buffer(Table.ColumnNames(Source)),
    ColumnCount = Table.ColumnCount(Source) - 1,
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Revenue", each List.Sum(List.Transform({0..ColumnCount}, (x)=> if Text.StartsWith(ListOfColumns{x},"4") then Record.ToList(_){x} else null))),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Cost of Revenue", each List.Sum(List.Transform({0..ColumnCount}, (x)=> if Text.StartsWith(ListOfColumns{x},"5") then List.RemoveLastN(Record.ToList(_),1){x} else null)))
in
    #"Added Custom1"

[dufoq3 · Accepted solution]

Hi @smallfires0628, similar approach here.

Result (added 2 columns to sample table)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRS0lEyNDAAkmbmYCaIsICzQIQRlBWrE63k7AIWAImYmoGETeHqTVE0gdTHAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Project Name" = _t, #"40567-service income" = _t, #"40785 - product income" = _t, #"40037 - additional income" = _t, #"50678 - materials cost" = _t, #"54678 - service cost" = _t, #"56890 - driver fee" = _t, #"56768 - gasoline fee" = _t, #"56780 - additional fee" = _t]),
    // You can delete this step if you have columns with correct types.
    ChangedToNumbers = Table.TransformColumns(Source, {}, each try Number.From(_) otherwise Text.From(_)),
    ColumnNames = List.Buffer(List.Transform(Table.ColumnNames(ChangedToNumbers), Text.Trim)),
    StepBack = ChangedToNumbers,
    Ad_Revenue = Table.AddColumn(StepBack, "Revenue", each List.Sum(Record.ToList(Record.SelectFields(_, List.Select(ColumnNames, (x)=> Text.StartsWith(x, "4"))))), type number),
    Ad_CostOfRevenue = Table.AddColumn(Ad_Revenue, "Cost of Revenue", each List.Sum(Record.ToList(Record.SelectFields(_, List.Select(ColumnNames, (x)=> Text.StartsWith(x, "5"))))), type number)
in
    Ad_CostOfRevenue

[Vijay_A_Verma · Accepted solution]

See the code below

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRS0lEyNDAAkmbmYCaIsICzQIQRlBWrE63k7AIWAImYmoGETeHqTVE0gdTHAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Project Name" = _t, #"40567-service income" = _t, #"40785 - product income" = _t, #"40037 - additional income" = _t, #"50678 - materials cost" = _t, #"54678 - service cost" = _t, #"56890 - driver fee" = _t, #"56768 - gasoline fee" = _t, #"56780 - additional fee" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project Name", type text}, {"40567-service income", Int64.Type}, {"40785 - product income", Int64.Type}, {"40037 - additional income", Int64.Type}, {"50678 - materials cost", Int64.Type}, {"54678 - service cost", Int64.Type}, {"56890 - driver fee", Int64.Type}, {"56768 - gasoline fee", Int64.Type}, {"56780 - additional fee", Int64.Type}}),
    ListOfColumns = List.Buffer(Table.ColumnNames(Source)),
    ColumnCount = Table.ColumnCount(Source) - 1,
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Revenue", each List.Sum(List.Transform({0..ColumnCount}, (x)=> if Text.StartsWith(ListOfColumns{x},"4") then Record.ToList(_){x} else null))),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Cost of Revenue", each List.Sum(List.Transform({0..ColumnCount}, (x)=> if Text.StartsWith(ListOfColumns{x},"5") then List.RemoveLastN(Record.ToList(_),1){x} else null)))
in
    #"Added Custom1"

[some_bih]

Hi @smallfires0628 

possible solution for new column below 

= if Text.StartsWith([Account], "4") then [YourAmountColumn]
else if Text.StartsWith([Account], "5") then [YourAmountColumn]
else null

Link for this function

[smallfires0628]

My table looks something like this: 

Project Name	40567-service income	40785 - product income	40037 - additional income	50678 - materials cost	54678 - service cost	56890 - driver fee	56768 - gasoline fee	56780 - additional fee	New column - Revenue	New Column - Cost of Revenue
AB	100	67	10	18	10	10	20	10
CD	200	56	15	18	15	10	10	10

If I understand correctly, the text.startswith function will look at a specific column (let's say the service income column) and add amounts based on if the values underneath it match a condition. What I want is something like this: if Column header starts with "4" then add values.