Forum Discussion

bamba98's avatar
bamba98
Helper 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 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:

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:
As you can see, I get True instead of 2018 Q3.

Does anyone know the correct Dax expresion?

 
 
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.

9 Replies

  • quantumudit's avatar
    quantumudit
    Super User
    5 years ago

    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.

    • bamba98's avatar
      bamba98
      Helper 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
        Super 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)