ZigZag pattern as new Column for ID

Question

Hi,I am looking for ZigZag pattern for the report,I have this&nbsp;I would like to show table like thisThanks&nbsp;

danextian · Answer

Hi damit230183​&nbsp;
Assuming the rows are ordered chronologically, create an index column in Power Query to identify which row comes first if there isn't any column in your table that indicates such

Then try this:
Pattern = VAR _CurrentID = 'Table'[id] -- Isolate all rows for the current ID VAR _RelatedRows = FILTER ( ALL ( 'Table' ), 'Table'[id] = _CurrentID ) -- Find the true starting row (highest Index = bottom row = Index 1) VAR _MaxIndex = MAXX ( _RelatedRows, 'Table'[Index] ) -- Extract the starting PrevOp (3 from Index 1) VAR _StartNode = LOOKUPVALUE ( 'Table'[PrevOp], 'Table'[id], _CurrentID, 'Table'[Index], _MaxIndex ) -- Concatenate CurrentOp from bottom to top (Index 1 then Index 0) VAR _Chain = CONCATENATEX ( _RelatedRows, 'Table'[CurrentOp], " -&gt; ", 'Table'[Index], DESC ) RETURN _StartNode &amp; " -&gt; " &amp; _Chain
Please see the attached pbix.&nbsp;
Note: solution was done with the help of AI.

damit230183 · Answer

Hi,Thanks for your response.So i am using direct query due to very high db volume (atleast 5 Mil everyday). So some of the syntax like Index, ConcatenateX etc I can not use.&nbsp;Thanks

danextian · Answer

Unfortunately, your requirement requires iteration which isn't supported in Direct Query. If you have that big of a data, I wouldn't do a full load each refresh but use incremental refresh instead.&nbsp;

shivekmaharaj · Answer

Hi damit230183​,Since you're using DirectQuery with roughly 5 million rows per day, I would change the approach rather than trying to reproduce the Power Query index logic in DAX.The pattern you want is not really a row-level calculation. To turn:3 -&gt; 88 -&gt; 9into:3 -&gt; 8 -&gt; 9you need to compare/traverse multiple rows for the same tagid.Microsoft's current DirectQuery guidance notes that calculated columns on relational DirectQuery tables are limited to row-level expressions that can be translated to the source. That makes this type of recursive/iterative chain-building a poor fit for a DirectQuery calculated column.For this volume, I would push the transformation upstream instead.Ideally, create a source-side table such as:tagid&nbsp; &nbsp; &nbsp; Pattern1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3 -&gt; 8 -&gt; 92&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 4 -&gt; 7 -&gt; 10 -&gt; 12and update that table incrementally as new operations arrive.If you cannot materialize another table, the next option would be a database view that derives the chain at the source. For example, on SQL Server/Azure SQL this can be implemented with recursive SQL logic that follows:PrevOp -&gt; CurrentOp -&gt; next CurrentOpfor each tagid.Microsoft's query folding guidance specifically recommends moving transformations into the source for DirectQuery when they cannot be folded efficiently.I would avoid generating an index in Power Query over 5M daily rows, because DirectQuery depends heavily on keeping the transformation foldable.If you tell me what database is behind the DirectQuery connection (SQL Server, Snowflake, Oracle, etc.), I can suggest the exact source-side pattern for building the chain.AI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.

Forum Discussion

ZigZag pattern as new Column for ID

4 Replies

Related content

Pattern Match DAX Column

Average of Average Pattern Inaccuracies

power query matching pattern

Split column based on pattern

remove string patterns within a column