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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.