Forum Discussion
Create a new table from existing table (Using DAX Commands)
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
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
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
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
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?
ThanksSure, 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
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
