Calculating the growth rate and defining the averages

[Difficultgrasp]

Hi, I have a power query dataset with the data for different companies and their long term debts in each of the given years. The companies and years are indicated in the rows and the long-term debt is indicated in columns. What I want is to look at the growth or the decrease in percentage of the long-term debt for each of the companies in comparison to the previous year. 

I tried to solve the issue with index columns, so the indexed numbers start from 1 for each of the companies and go from the first year till the last year in the dataset. Then, I duplicated the indexed column and subtracted 1 from each of the numbers in the indexed column so the values started from 0. At the end, I merged the tables (company name and indexed column) using left outer, but in this case I lose the first year in the dataset.

Adding 1 to each of the numbers in the indexed column does not make sense because in this case the growth rate will be shown not for the required year, but for "the required year - 1". 

Also, sometimes the data repeats for some ot the companies because there are other parameters which differ. And this is another reason why adding 1 does not make sense.

Here is how the data looks like:
Company                Acquisition            Year              LTD              Indexed Number              New Indexed Number

Company A                    1997              2000              10                           1                                           0

Company A                    1997              2001              20                           2                                           1

Company A                    1997              2002              15                           3                                           2

Company A                    1997              2003              18                           4                                           3

     ...                                 ...                     ...                 ...                           ...                                           ...

Company A                    1997              2023              40                          24                                         23

Company A                    2008              2000              10                           1                                           0

Company A                    2008              2001              20                           2                                           1

Company A                    2008              2002              15                           3                                           2

Company A                    2008              2003              18                           4                                           3

     ...                                 ...                     ...                 ...                           ...                                           ...

Company A                    1993              2023              40                          24                                         23

Company B                    1993              2000              50                           1                                           0

Company B                    1993              2001              80                           2                                           1

Company B                    1993              2002             150                          3                                           2

Company B                    1993              2003             120                          4                                           3

     ...                                 ...                     ...                 ...                           ...                                           ...

Company B                    1993              2023             400                         24                                         23

Maybe the solution is easy, but I am new to the power query and do not know how to handle this problem. 

At the same time, I am looking at what portion of the total debt is equal to the current long-term debt for the companies and how it changes with time. 

I would also like to see the average long-term debt growth rate for each of the companies and its average change as a portion of total debt. 

However, I am still stuck on the first point and do not know how to proceed. 

Thank you in advance! 

[v-sdhruv]

Hi @Difficultgrasp ,
Since we didnt hear back, we would be closing this thread.
If you need any assistance, feel free to reach out by creating a new post.

Thank you for using Microsoft Community Forum

[v-sdhruv]

Hi @Difficultgrasp ,

Just wanted to check if you got a chance to review the suggestions provided and whether that helped you resolve your query?

Thank You @ronrsnfld  for your prompt response to the query.

[ronrsnfld · Accepted solution]

I'm not sure exactly what you want for results, as I was unable to clearly understand your description and you did not provide a sample of your desired results from your posted data.

But perhaps you can build on the below.

If it does not provide what you want for part of your problem, please provide the desired results that would obtain from your posted data.

In Power query for the year over year change per company and acquisition date you can

• Group by Company and Acquisition Date
• Add a shifted LTD column to compare the previous year
• Calculate the percent change

let

//Change next line to reflect actual data source
    Source=Table,
    colNames = Table.ColumnNames(Source),
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Company", type text},
        {"Acquisition", Int64.Type}, 
        {"Year",Int64.Type},
        {"LTD", type number}}),
    
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Company", "Acquisition"}, {
        {"% LTD Change", (t)=>
            [a=Table.FromColumns(
                Table.ToColumns(t)
                & {{null} & List.RemoveLastN(t[LTD])},
                Table.ColumnNames(t) & {"Shifted LTD"}),
             b=Table.AddColumn(a, "Percent LTD Change",each [LTD]/[Shifted LTD],Percentage.Type),
             c=Table.RemoveColumns(b,{"Shifted LTD"})
                ][c], type table[Year=Int64.Type, LTD=Int64.Type, Percent LTD Change=Percentage.Type]}}),
    
    #"Expanded % LTD Change" = Table.ExpandTableColumn(#"Grouped Rows", "% LTD Change", {"Year", "LTD", "Percent LTD Change"})

in
    #"Expanded % LTD Change"

Results from your data

[v-sdhruv]

Hi @Difficultgrasp ,

Just wanted to check if you got a chance to review the suggestions provided and whether that helped you resolve your query?

Thank You

[v-sdhruv]

Hi @Difficultgrasp ,
Just wanted to check if you got a chance to review the suggestions provided and whether that helped you resolve your query?

Thank You @SundarRaj and @Irwan  for your prompt response to the query.

[SundarRaj]

Hi @Difficultgrasp,
I've tried to put together all the transformations as mentioned above. Hope this helps. Do let me know in case of any confusion or in case there are any corrections. Thanks

Regards,

[Irwan]

hello @Difficultgrasp 

looks like your step is correct.

using index for Year of each company and each Acquisition Year then merge to get next year LTD.

the diff can be each year can be [LTD]-[LTDprevious]

Yes, as you said, the first year LTD will be gone because there is no same index. however, i think you can keep the first year LTD when you are expanding column after merge queries by keeping the LTD column. So the first year LTD will be in LTD column.

for "portion of the total debt" and "average long-term debt growth rate", this should be done either in power bi or excel or any other visualization tools after you import/load your data.

power query possible to do the calculation but i believe power query is mainly for transforming table.

Hope this will help.

Thank you.