cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bamba98
Helper I
Helper I

Select single cell from table to another table

Hi,

 

I have a messy file that i download every quarter. However, that download looks the same every quarter and I would like to upload that file in power bi and create a new table from the current table. In that file, there is a single cell with the name of the period, i want to extract that information into a column in the new table. I.e., the column looks like this:

bamba98_1-1601293514807.png

In the new table, I want the period 2018 Q3 to be visible in the column. I tried to following DAX:

Period = SELECTCOLUMNS('2020Q1';"Period";CONTAINS(VALUES('2020Q1');'2020Q1'[Column3];"2020 Q1"))

and i get the following result:
bamba98_0-1601293861006.png
As you can see, I get True instead of 2018 Q3.

Does anyone know the correct Dax expresion?

 
 
1 ACCEPTED SOLUTION

@bamba98  So, you want that in another table? Not as a column in the same table?

 

Ok, try this one:

 

Period =
VAR Tab =

FILTER(
ALL('Table'[Column1]),
CONTAINSSTRING('Table'[Column1],"Q1") ||
CONTAINSSTRING('Table'[Column1],"Q2") ||
CONTAINSSTRING('Table'[Column1],"Q3") ||
CONTAINSSTRING('Table'[Column1],"Q4")
)

RETURN
LOOKUPVALUE('Table'[Column1],'Table'[Column1],Tab)

 

This will work when you want the "Period" as a calculated column. It should work if I have understood your requirements correctly. Worked in my case.

View solution in original post

9 REPLIES 9
Quantum_Udit
Advocate V
Advocate V

As far as I understand, it seems you want to automate the process and the period present in that cell is also going to change over time. Correct me if I am wrong in understanding the problem.

 

I would suggest, give a try to LOOKUPVALUE() function and let me know if you got the solution.

Hi @Quantum_Udit , you understand the problem correctly. However, I don't see how the LOOKUPVALUE would work as there is not reference column....

 

I would like to have something like this....period = IF COLUMN IN TABLE X CONTAINS CELL WITH "Q1"||"Q2"||"Q3"||"Q4" Return CELL VALUE

In this example, it should return 2018 Q3.

Try this DAX formula to create the "Period" calculated column and let me know if it works.

 

Period =
FILTER(
      ALL( 'Table'[Messy Column] ),
     CONTAINSSTRING(  'Table'[Messy Column] , "Q1" ) ||

     CONTAINSSTRING(  'Table'[Messy Column] , "Q2" ) ||
     CONTAINSSTRING(  'Table'[Messy Column] , "Q3" ) ||
     CONTAINSSTRING(  'Table'[Messy Column] , "Q4" )

)

 

This worked in my case and I hope you will also get a positive outcome.

 

(Assuming that you are always going to have a quarter level of data each time)

@Quantum_Udit It does not work for me. I get the following error: "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."

If I am not wrong then, you got a single column with messy value and there is only one cell that contains the "Q3" string period value.

The formula will work only if the above condition is met.

@Quantum_Udit yes correct. I have a table that contains a column. In that column there is only one cell specifying the period. I want to extract that single cell into a column in another table

@bamba98  So, you want that in another table? Not as a column in the same table?

 

Ok, try this one:

 

Period =
VAR Tab =

FILTER(
ALL('Table'[Column1]),
CONTAINSSTRING('Table'[Column1],"Q1") ||
CONTAINSSTRING('Table'[Column1],"Q2") ||
CONTAINSSTRING('Table'[Column1],"Q3") ||
CONTAINSSTRING('Table'[Column1],"Q4")
)

RETURN
LOOKUPVALUE('Table'[Column1],'Table'[Column1],Tab)

 

This will work when you want the "Period" as a calculated column. It should work if I have understood your requirements correctly. Worked in my case.

View solution in original post

It works now! Thanks @Quantum_Udit 

Congratulations !!! @bamba98 

Would really appreciate your kudos 😊

 

Connect with me over

 

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.