Compare data from two files and fill a new column?

[montiell]

Hi everybody,

I am new to power bi, and I have been requested to create a report for my job. I am currently on the need to find a way of simplifiying my data. Lets say i have a excel csv file with data that contain a column with 107 different sub categories(over 17k entries). This sub categories falls into 8 main categories that I would like to create a new column and somehow create a Dax formula that if the value is equal to VALUE1 or VALUE2 or VALUE3... VALUE107, Fill the new colum with CATEGORY1 or CATEGORY2...CATEGORY8. What I did i have My main report data sheet on power bi and I created another excel file with the Main category and the subcategory and i would like to accomplish the following:

If File1 SubCategory Column is equal to File2 SubCategory Column, fill the Main Category value from File2 MainCategory on New column on File1

I am not sure if this makes any sense but I do hope it does!

[montiell]

Thank you all for your help!

[v-kelly-msft · Accepted solution]

Hi @montiell ,

I created 2 sample table to explain how to realize what you need:

File 1File 2

For question 1,if you wanna create a new column to group by the values,take the steps as below:

Go to query editor>add column>conditional column>then fill in the blanks with your requirement;

For question 2:

Create a calculated column as below:

main category = LOOKUPVALUE('File 2'[main category],'File 2'[subcategory],'File 1'[subcategory],blank())

And you will see:

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[mahoneypat]

I think what you are describing in a called a Groups column in Power BI.  If so, it's easy.  Please see this article.

https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-grouping-and-binning

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat