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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
quantumudit
Continued Contributor
Continued Contributor

@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
quantumudit
Continued Contributor
Continued Contributor

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 @quantumudit , 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.

quantumudit
Continued Contributor
Continued Contributor

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)

@quantumudit 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."

quantumudit
Continued Contributor
Continued Contributor

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.

@quantumudit 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

quantumudit
Continued Contributor
Continued Contributor

@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.

It works now! Thanks @quantumudit 

quantumudit
Continued Contributor
Continued Contributor

Congratulations !!! @bamba98 

Would really appreciate your kudos 😊

 

Connect with me over

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors