How to make new columns for several years & populate those columns with calc using respective year

[MarkD1722]

I have a table and am using power query to add15 columns, each for a year from 2018-2032.  This set of years is in its own table named "Year List."  Each column needs to perform a calculation on each row that uses the year of the columns.  For example, in column "2018," each row is calculated with 2018 in the context.  In column 2019, the calculation uses 2019, and so forth.  I am not anywhere near as familiar with Power Query's M language compared to that of DAX, so I have started the process of adding 15 column individually, but I know that it can be done with a single step.  Can someone help me with the basic structure for doing this in a single step (some kind of nested structure).  I have the structure of the column calculation.  I am looking for how to replace my 15 "Add column" steps with just one, and have that calculation work for each column in the process.  Can anyone point me to the best response out there, or provide me with some guidance on how to do this.

Also, there will be errors and I have a step to remove them for each column.  I assume I can replace them all with a single "Remove errors" step for all the columns.  If anyone know that will not work, please let me know. 

Thank you in advance for any help you can provide.

[v-jingzhan-msft]

Hi @MarkD1722 

If you do want to add the 15 columns with Power Query, you can first add a column to have all years from the "Year List" table, then add a second column to make the calculation using Year column. At last pivot Year column and choose calculation results as values in rows. This will be simpler than making calculation after having all 15 columns. Pivot columns feature description - Power Query | Microsoft Learn

To Remove errors in multiple columns, you can hold on Shift or Ctrl key then select multiple columns at the same time, then use replace errors feature to modify all selected columns at the same time. 

Otherwise if you want to make the calculation with DAX, remaining the table as unpivot that have all Years in a single column will be better. This will make DAX calculation easier. Just as @lbendlin has suggested. 

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[MarkD1722]

Not sure that I was sufficiently clear on my intent.  So, I have a calculation on each row of a table of data (in this case, it is a set of conditionals that then produce a category for each row).  That calculation must be performed on the data for each year for the years of 2018-2032, as the category may change depending on the year.  This is not a calendar table or anything like that...so I cannot just pivot a year list (unless I am mistaken).  But I have seen other instances where it appears that several columns can be set up and each column be filled out with whatever calculation is being used using a single formula in the advanced query editor.   Once these columns are filled out, the whole table can be de-pivoted to produce a narrow table that I will use for visualizations.

Thank you for the instruction on the Remove errors part.

[v-jingzhan-msft]

Hi @MarkD1722 

I just generated some calendar dates as an example for calculation. Sorry I may have misunderstood it before. Can you provide some dummy data to show what you originally have and what you finally want to get? What is the calculation you want to do by using those years? By knowing this, we can try to provide a more definite solution. 

Best Regards,
Jing

[MarkD1722]

Thanks, @v-jingshan-msft   Below is some example data I condensed into a single spreadsheet for the ease of providing an image.  I ultimately want to detect for each item when there is a spend event in each year.  I am looking for help with Step 2.

1)  I start with a spend table (fact table) that includes the item number, spend values, and associated dates (I omitted the spend values).  There can be one or more spend dates per item.  The Item is the foreign key in the relationship with the next table I will describe.

2)  There is another fact table of items (one row per item; has lots of columns) where the item is the primary key in the relationship with the spend table.  I want to add columns to this item table...one column for each of several years (2018-2032)... and populate all the new cells in these year columns with a result -- either the year if there was a spend event (in the spend table) or X (or null or something else) if there was not any spend event.   (Note that there are other conditions I intend to employ using data from within the item table, which is why I cannot just pivot the spend table, but I can handle those as "nested if" statements, so I will keep it simple to this particular condition of determining activity using the spend table because I don't know how to pull in data from another table with M language.)  This interim step should result in a table with several "year" columns and those columns' cells have either the respective year or "X" populating them depending on if there was a spend event.  If there are any errors (and there are), I will be replacing them with "X" as well.

3)  Ultimately, I will de-pivot that item table, filter out the rows with "X", and return a table where there are one or more rows for each item, but with a single "Active Years" column, where each row contains the year that we had determined as active.

4)  NOT SHOWN:  I have a Year List (i.e., a table of just years 2018-2032) with which I can reference for making columns (if I knew how to do that).

So, in summary, I have already created the query that does all this work by making each indivdiual column, removing errors, etc.  But I figured there was a more streamlined, elegant way of doing the AddColumn part where the query just iterates my "active year determination" calculation as it creates each year column (in Step 2).

Hopefully, that explains my idea more fully.  Thank you for taking time to help.

Mark

[v-jingzhan-msft]

Hi @MarkD1722 

Sorry for the late response. I think of two methods to get the final ACTIVE YEARS column in ITEM table, please see detailed steps in the attachment. I don't think adding 15 new year columns will make the calculation simpler so I didn't 100% follow your logic.

In both methods, I add an Active Year column in 'Spend table' to extract the year from [DATE] column. I use Merge queries feature to bring Active Year data from 'Spend table' into 'Item table'. This is a very common and recommended feature in Power Query to pull in data from another table based on one or multiple matching columns. In this case, [ITEM] column is the matching column. To learn more about it, please read Merge queries overview - Power Query | Microsoft Learn

Hope this would be helpful. Let me know if you have any questions. 

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[lbendlin]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkvMKU1Vio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each {2018..2032}),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Custom", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv)),
    #"Promoted Headers" = Table.PromoteHeaders(#"Split Column by Delimiter", [PromoteAllScalars=true])
in
    #"Promoted Headers"

yes, you can pivot a list into columns.  There are many ways to do that. here's a pedestrian version.

[lbendlin]

 am using power query to add15 columns, each for a year from 2018-2032.  

Design red flag.  Power BI is not Excel.  Keep your data unpivoted and let the visuals do the pivoting work for you.

[MarkD1722]

I need to set these up so I can then de-pivot them (along with a bunch of other data) and get them in to a narrow table.