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, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare 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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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