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

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