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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

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
quantumudit
Skilled Sharer
Skilled Sharer

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.

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

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

@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 

Congratulations !!! @bamba98 

Would really appreciate your kudos 😊

 

Connect with me over

 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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

Top Solution Authors