Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Create a new table from existing table (Using DAX Commands)

Hello my saviours, Hope you are all good. I need to create a new table based on calculation from Table 1 My table looks like this  Table 1: IDNO Store_Number Slow  AA1 S1 y AA1 S2 n...
Need Help
Tips and Tricks
Tutorial Requests
  • AlB's avatar
    AlB
    5 years ago

    Anonymous 

    It can actually be done in a far, far easier way if you have a column that specifies order in Table1 (or add an index as suggested by nandic). In my previous take, I somehow (must have been asleep) assumed you wanted a solution that would use the "Affected by" column in "Table1 (updated)". This only overcomplicates things unnecessarily

    Table2 =
GENERATE (
    Table1,
    SELECTCOLUMNS (
        CALCULATETABLE (
            DISTINCT ( Table1[Store_Number] ),
            Table1[Index] < EARLIER ( Table1[Index] ),
            Table1[Slow] = "y",
            ALLEXCEPT ( Table1, Table1[IDNO] )
        ),
        "Affected by", Table1[Store_Number]
    )
)

     

    Please mark the question solved when done and consider giving kudos if posts are helpful.

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.

    Cheers 

     

nandic's avatar
nandic
Resident Rockstar
5 years ago

Anonymous ,

First table can be updated with new column "Affected by". In Power Query just add index for this table.
After that add this calculated column:

Affected by =
CONCATENATEX (
    SELECTCOLUMNS (
        FILTER (
            Table_Stores,
            Table_Stores[Slow ] = "y"
                && Table_Stores[Index] < EARLIER ( Table_Stores[Index] )
        ),
        "Store", Table_Stores[Store_Number]
    ),
    [Store],
    ","
)

Screenshot below:



I just didn't understand if that is final result or you need also last table for making relationship?

Cheers,
Nemanja

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    hey nandic,
    Sorry for not making the question clearly, I need the table 2 part where each faulty stores is stored as a record..For example since Store S4 was affected by S1 and S3 and new table having that in individual lines has to be created like this 

    AA1S4S1
    AA1S4S3


    But thanks a lot for the calculated column code that i desgined using some other logic .Your logic is simple and elegant 

    Thanks again

    • AlB's avatar
      AlB
      Community Champion
      5 years ago

      Hi Anonymous 

      This would be faaar easier in PQ but if you want it in DAX, you can create a calulated table. Table1 in the code is actually what you show as Table1 updated:

       

      Table1B =
GENERATE (
    SUMMARIZE ( Table1, Table1[IDNO], Table1[Store_Number] ),
    VAR affectedBy_ =
        CALCULATE ( DISTINCT ( Table1[Affected by] ) )
    VAR numItems_ =
        IF (
            LEN ( affectedBy_ ) = 0,
            0,
            LEN ( affectedBy_ ) - LEN ( SUBSTITUTE ( affectedBy_, ",", "" ) ) + 1
        )
    VAR baseT_ =
        GENERATESERIES ( 1, numItems_ )
    VAR resT_ =
        ADDCOLUMNS (
            baseT_,
            "NewColumn",
                VAR itemNum_ = [Value]
                VAR pos1_ =
                    IF (
                        itemNum_ = 1,
                        0,
                        FIND (
                            UNICHAR ( 160 ),
                            SUBSTITUTE ( affectedBy_, ",", UNICHAR ( 160 ), itemNum_ - 1 ),
                            1,
                            0
                        )
                    )
                VAR pos2_ =
                    VAR foundAt_ =
                        FIND (
                            UNICHAR ( 160 ),
                            SUBSTITUTE ( affectedBy_, ",", UNICHAR ( 160 ), itemNum_ ),
                            1,
                            0
                        )
                    RETURN
                        IF ( foundAt_ = 0, LEN ( affectedBy_ ) + 1, foundAt_ )
                VAR extracted_ =
                    MID ( affectedBy_, pos1_ + 1, pos2_ - pos1_ - 1 )
                RETURN
                    extracted_
        )
    RETURN
        SELECTCOLUMNS ( resT_, "NewColumn", [NewColumn] )
)

       

       

      Please mark the question solved when done and consider giving kudos if posts are helpful.

      Contact me privately for support with any larger-scale BI needs, tutoring, etc.

      Cheers 

       

      • AlB's avatar
        AlB
        Community Champion
        5 years ago

        Anonymous 

        And the same in PQ (much simpler). Copy the M code below in an empty query to see the steps.  #"Changed Type" is your Table1, the processing starts at #"Added Custom"

        let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnQ0VNJRCgYRlUCsFKsDFzMCEnkQSSRRY6hKVFETuKgOUAWShCncEKCEDlAdkpwZsiawXCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [IDNO = _t, Store_Number = _t, #"Slow " = _t, #"Affected by" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"IDNO", type text}, {"Store_Number", type text}, {"Slow ", type text}, {"Affected by", type text}}),
    
    #"Added Custom" = Table.AddColumn(#"Changed Type", "NewColumn", each Text.Split([Affected by],",")),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "NewColumn"),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Slow ", "Affected by"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([NewColumn] <> ""))
in
    #"Filtered Rows"

         

        Please mark the question solved when done and consider giving kudos if posts are helpful.

        Contact me privately for support with any larger-scale BI needs, tutoring, etc.

        Cheers 

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi nandic,
    The affecetd_by column solution u gave is running for 30 mins since my dataset is huge ( in millions) is there a way to not use the filter() that i belive is increasing the running time

    thanks again for your time