Filter data by columns table vs excel file

[nitrox]

Hi Everyone,

I have a table with the following columns:

Columns:

Register (DateTime) | Type | User | Amount

From this data, I can assemble a table grouping / filtering data by date and adding the rest of the data without problems. Now, I have been asked to unify some information obtained from an Excel file. This file has the following structure.

Columns:

Id | User | January | February | March | April | ... | Total

What I have been asked now, is that I can relate the month of the field (Register) with the column of the corresponding month in the Excel file. Is this possible?

Many thanks!!!

[MAwwad]

Yes, it is possible to relate the month of the "Register" field with the corresponding column in the Excel file. One way to do this is to add a new column to the table that extracts the month from the "Register" column using a formula or a function, and then use that new column to map the month to the corresponding column in the Excel file. For example, you can use the MONTH() function in Excel to extract the month from the "Register" column, and then use a VLOOKUP() function to find the corresponding column in the Excel file based on the extracted month.

[nitrox · Accepted solution]

Hi @MAwwad. First of all, thank you for your help.

Finally I have been able to solve it in another way (after looking for all kinds of solutions). 

I have found the solution by applying a modification of the table to apply the unpivot option to the columns (months). In this way, I converted the columns into rows and was able to create the corresponding measures.

https://www.thebiccountant.com/2015/08/12/how-to-pivot-multiple-measurescolumns-in-power-query/