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

[Anonymous]

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
AA1	S3	y
AA1	S4	y
AA1	S5	n
AA1	S6	y

Because of slow offloading at stores other stores get affected and hence a new coulmn needs to be created as below showing the problem causing stores .For example : S4 is affected because of slow offloading at s1 and s3 as both these storesd are marked as "y"
Table 1 (Updated)

IDNO	Store_Number	Slow	Affected by
AA1	S1	y
AA1	S2	n	S1
AA1	S3	y	S1
AA1	S4	y	S1,S3
AA1	S5	n	S1,S3,S4
AA1	S6	y	S1,S3,S4

Now i need to code for Affected by Column to display the stores  and also create a new table (Like below) that records each store affected by in a row such as this 

Table 2:

IDNO	Store_Number	Affected by
AA1	S2	S1
AA1	S3	S1
AA1	S4	S1
AA1	S4	S3
AA1	S5	S1
AA1	S5	S3
AA1	S5	S4
AA1	S6	S1
AA1	S6	S3
AA1	S6	S4

By creating this new table i m trying to establish a one to many relationship from which i can then pull up data for visulaization purpose. Thanks a ton 🙂

[AlB · Accepted solution]

@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 

[erickpasana]

NewTable = OriginalTable

I just found out...

[nandic]

@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]

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

[Anonymous]

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 

AA1	S4	S1
AA1	S4	S3

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]

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 

[Anonymous]

Just wow :O... My affected_by Column code by @nandic  hasnt finished running yet will update and try this once it is done...

Again just wow and thanks..You guys are legends 

[AlB]

@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]

@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]

@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 

[CNENFRNL]

@Anonymous , from the perspective of Power Query, Table 1 (Updated) and Table 2 is identical but only by different transformations.

I'm having difficulty understanding what you want.🤔