Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
nitrox
Helper I
Helper I

Filter data by columns table vs excel file

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!!!

1 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/

View solution in original post

2 REPLIES 2
MAwwad
Super User
Super User

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.

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/

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.