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 

     

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 

 

  • nandic's avatar
    nandic
    Resident Rockstar
    5 years ago

    AlB , ğŸ™ŒğŸ™ŒğŸ™Œ, well done for dax solution!
    If there was no "Affected by" column (created using dax), would it be possible to create full solution only based in Power Query (M language)?
    In other words, would it be possible to create "Affected by" column using Power Query?
    Thanks

    • AlB's avatar
      AlB
      Community Champion
      5 years ago

      nandic 

      Sure, PQ can do a whole lot of stuff. Copy this in a blank query to see the steps. It would probably be slow for large tables though

      let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnQ0VNJRCgYRlUqxOnABIyCRhyxgjK7CBF3AFF2LGURFLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [IDNO = _t, Store_Number = _t, Slow = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"IDNO", type text}, {"Store_Number", type text}, {"Slow", type text}}),

    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Affected by", each Text.Combine(Table.SelectRows(#"Added Index", (inner)=>inner[IDNO]=[IDNO] and inner[Slow]="y" and inner[Index]<[Index])[Store_Number], ",")),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"})
in
    #"Removed Columns"

       

      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