Forum Discussion

bamba98's avatar
bamba98
Icon for Helper I rankHelper I
5 years ago
Solved

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...
DAX
Table
  • quantumudit's avatar
    quantumudit
    5 years ago

    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.

bamba98's avatar
bamba98
Icon for Helper I rankHelper I
5 years ago

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's avatar
quantumudit
Icon for Super User rankSuper User
5 years ago

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)

  • bamba98's avatar
    bamba98
    Icon for Helper I rankHelper I
    5 years ago

    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's avatar
      quantumudit
      Icon for Super User rankSuper User
      5 years ago

      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.

      • bamba98's avatar
        bamba98
        Icon for Helper I rankHelper I
        5 years ago

        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