Custom Columns Based on Other Rows in Table

[NewbNeedHelp]

I tried searching for the answer to this, but couldn't find an exact solve. I want to create custom columns based on other rows in the table. 

Here is a masked version of what my data looks like that I mocked up in Excel (except there are many more accounts in the real data). Columns A:G are available in my Power BI dash. Columns H:K are what I'm trying to make as calculated columns (not a measure).

Thank you for any help!! Maybe this will be really easy for someone? Even though it's impossible for me :).

[jgeddes · Accepted solution]

You can do this in Power Query.
Here is one example of a possiblesolution that you can paste into the Advanced Editor of a blank query and then step through the solution.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rZTLqsIwFEV/pXQs2DxqmmFzBxcEH+hQHOT2BilIWmLF3zc6qfFYadKMmtJ0cfYOWYdDWlZVc9VdUqaztGzbs7rYBaF5YR8Izwma4wyjx8dr1yQ7pdVN/dvXpdQJxnaRE5weZy5oY6Q+eZM4hqR9Z+TtTxlTe+MQZQDXJ+ScvYLwAIg8N1H6LaIHihNIeo/ogSOUANyvkW2Uuba1VjKoL4YyQFvZuhpvFGPF8BlSnvNX0OOHH6krde4h9LmBL4ZrGkvhDIZyKho9Ditg2X09YzFFBqtZq6qTxg7lA6LEvScilglENBOIuCZwE04xwVvECTfuU8QJJhCxTCCimkBEM4FzhsEmcGoKNQGoKNgEbj2hJhCTTHC8Aw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Account Name" = _t, #"Product Name" = _t, #"Contract Number" = _t, #"End Date" = _t, #"End Reason" = _t, #"Renewal Month Year" = _t, Amount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Account Name", type text}, {"Product Name", type text}, {"Contract Number", Int64.Type}, {"End Date", type date}, {"End Reason", type text}, {"Renewal Month Year", type text}, {"Amount", Int64.Type}}),
    Custom1 = Table.Buffer(Table.Sort(#"Changed Type", {{"Account Name", Order.Ascending}, {"Contract Number", Order.Ascending}})),
    #"Grouped Rows" = Table.Group(Custom1, {"Account Name", "Contract Number"}, {{"_nestedTable", each _, type table [Account Name=nullable text, Product Name=nullable text, Contract Number=nullable number, End Date=nullable date, End Reason=nullable text, Renewal Month Year=nullable text, Amount=nullable number]}}),
    #"Grouped Rows1" = Table.Group(#"Grouped Rows", {"Account Name"}, {{"_inner", each _, type table [Account Name=nullable text, Contract Number=nullable number, _nestedTable=table]}}),
    Custom2 = Table.TransformColumns(#"Grouped Rows1", {{"_inner", each Table.AddIndexColumn(_, "Index", 0, 1, Int64.Type)}}),
    #"Expanded _inner" = Table.ExpandTableColumn(Custom2, "_inner", {"Contract Number", "_nestedTable", "Index"}, {"Contract Number", "_nestedTable", "Index"}),
    Custom3 = Table.AddColumn(#"Expanded _inner", "Previous Contract", each try Table.SelectRows(#"Expanded _inner", (x)=> x[Account Name] = [Account Name])[Contract Number]{[Index]-1} otherwise null, Int64.Type),
    Custom4 = Table.AddColumn(Custom3, "Next Contract", each try Table.SelectRows(#"Expanded _inner", (x)=> x[Account Name] = [Account Name])[Contract Number]{[Index]+1} otherwise null, Int64.Type),
    #"Removed Columns" = Table.RemoveColumns(Custom4,{"Index"}),
    #"Expanded _nestedTable" = Table.ExpandTableColumn(#"Removed Columns", "_nestedTable", {"Product Name", "End Date", "End Reason", "Renewal Month Year", "Amount"}, {"Product Name", "End Date", "End Reason", "Renewal Month Year", "Amount"}),
    Custom5 = Table.AddColumn(#"Expanded _nestedTable", "Previous Amount", each try Table.SelectRows(#"Expanded _nestedTable", (x)=>x[Account Name] = [Account Name] and [Previous Contract] = x[Contract Number] and x[Product Name] = [Product Name])[Amount]{0} otherwise null, type number),
    Custom6 = Table.AddColumn(Custom5, "Next Amount", each try Table.SelectRows(Custom5, (x)=>x[Account Name] = [Account Name] and [Next Contract] = x[Contract Number] and x[Product Name] = [Product Name])[Amount]{0} otherwise null, type number),
    #"Changed Type1" = Table.TransformColumnTypes(Custom6,{{"Contract Number", Int64.Type}, {"Product Name", type text}, {"End Date", type date}, {"End Reason", type text}, {"Renewal Month Year", type text}, {"Amount", type number}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Changed Type1",{"Account Name", "Contract Number", "Product Name", "End Date", "End Reason", "Renewal Month Year", "Amount", "Previous Contract", "Previous Amount", "Next Contract", "Next Amount"})
in
    #"Reordered Columns"

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.

[NewbNeedHelp]

https://send.vis.ee/download/afe97e13730f01e5/#XonmYFJAlAFhMu0AIwYvfg

Let me know if this works!

[Ashish_Mathur]

Expired link message.

[NewbNeedHelp]

https://send.vis.ee/download/fa982b520c37606c/#MpoVUB5ityGYmkzbtorhmQ

Here you go!!

[jgeddes · Accepted solution]

You can do this in Power Query.
Here is one example of a possiblesolution that you can paste into the Advanced Editor of a blank query and then step through the solution.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rZTLqsIwFEV/pXQs2DxqmmFzBxcEH+hQHOT2BilIWmLF3zc6qfFYadKMmtJ0cfYOWYdDWlZVc9VdUqaztGzbs7rYBaF5YR8Izwma4wyjx8dr1yQ7pdVN/dvXpdQJxnaRE5weZy5oY6Q+eZM4hqR9Z+TtTxlTe+MQZQDXJ+ScvYLwAIg8N1H6LaIHihNIeo/ogSOUANyvkW2Uuba1VjKoL4YyQFvZuhpvFGPF8BlSnvNX0OOHH6krde4h9LmBL4ZrGkvhDIZyKho9Ditg2X09YzFFBqtZq6qTxg7lA6LEvScilglENBOIuCZwE04xwVvECTfuU8QJJhCxTCCimkBEM4FzhsEmcGoKNQGoKNgEbj2hJhCTTHC8Aw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Account Name" = _t, #"Product Name" = _t, #"Contract Number" = _t, #"End Date" = _t, #"End Reason" = _t, #"Renewal Month Year" = _t, Amount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Account Name", type text}, {"Product Name", type text}, {"Contract Number", Int64.Type}, {"End Date", type date}, {"End Reason", type text}, {"Renewal Month Year", type text}, {"Amount", Int64.Type}}),
    Custom1 = Table.Buffer(Table.Sort(#"Changed Type", {{"Account Name", Order.Ascending}, {"Contract Number", Order.Ascending}})),
    #"Grouped Rows" = Table.Group(Custom1, {"Account Name", "Contract Number"}, {{"_nestedTable", each _, type table [Account Name=nullable text, Product Name=nullable text, Contract Number=nullable number, End Date=nullable date, End Reason=nullable text, Renewal Month Year=nullable text, Amount=nullable number]}}),
    #"Grouped Rows1" = Table.Group(#"Grouped Rows", {"Account Name"}, {{"_inner", each _, type table [Account Name=nullable text, Contract Number=nullable number, _nestedTable=table]}}),
    Custom2 = Table.TransformColumns(#"Grouped Rows1", {{"_inner", each Table.AddIndexColumn(_, "Index", 0, 1, Int64.Type)}}),
    #"Expanded _inner" = Table.ExpandTableColumn(Custom2, "_inner", {"Contract Number", "_nestedTable", "Index"}, {"Contract Number", "_nestedTable", "Index"}),
    Custom3 = Table.AddColumn(#"Expanded _inner", "Previous Contract", each try Table.SelectRows(#"Expanded _inner", (x)=> x[Account Name] = [Account Name])[Contract Number]{[Index]-1} otherwise null, Int64.Type),
    Custom4 = Table.AddColumn(Custom3, "Next Contract", each try Table.SelectRows(#"Expanded _inner", (x)=> x[Account Name] = [Account Name])[Contract Number]{[Index]+1} otherwise null, Int64.Type),
    #"Removed Columns" = Table.RemoveColumns(Custom4,{"Index"}),
    #"Expanded _nestedTable" = Table.ExpandTableColumn(#"Removed Columns", "_nestedTable", {"Product Name", "End Date", "End Reason", "Renewal Month Year", "Amount"}, {"Product Name", "End Date", "End Reason", "Renewal Month Year", "Amount"}),
    Custom5 = Table.AddColumn(#"Expanded _nestedTable", "Previous Amount", each try Table.SelectRows(#"Expanded _nestedTable", (x)=>x[Account Name] = [Account Name] and [Previous Contract] = x[Contract Number] and x[Product Name] = [Product Name])[Amount]{0} otherwise null, type number),
    Custom6 = Table.AddColumn(Custom5, "Next Amount", each try Table.SelectRows(Custom5, (x)=>x[Account Name] = [Account Name] and [Next Contract] = x[Contract Number] and x[Product Name] = [Product Name])[Amount]{0} otherwise null, type number),
    #"Changed Type1" = Table.TransformColumnTypes(Custom6,{{"Contract Number", Int64.Type}, {"Product Name", type text}, {"End Date", type date}, {"End Reason", type text}, {"Renewal Month Year", type text}, {"Amount", type number}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Changed Type1",{"Account Name", "Contract Number", "Product Name", "End Date", "End Reason", "Renewal Month Year", "Amount", "Previous Contract", "Previous Amount", "Next Contract", "Next Amount"})
in
    #"Reordered Columns"

[NewbNeedHelp]

This worked!! But I'm wondering - Is there any way to do it with calculated columns instead?

[jgeddes · Accepted solution]

The attached file shows both the Power Query solution and a solution with calculated DAX columns. The DAX solution is not as robust as the Power Query one and would be more easily broken if the data changes.