Forum Discussion
Power Query fill in template based on different tabs
Hi ,This is a fantastic showcase of how to dynamically switch between templates using Parameters! Using
List.Accumulate to clean up bullets is also a very creative "native M" approach.I noticed you mentioned two specific pain points: simulating Regex and handling row shifts when splitting multiple columns by new lines.
Here are two "Super User" patterns that might solve those last 20% of manual work for you.
1. Solving the "Row Shift" Issue (Multi-Column Split)
You mentioned that splitting text by lines for more than 1 column causes shifts. This happens because standard splitting creates a Cartesian product (multiplication of rows).
To keep lines aligned across multiple columns (e.g., Description and Comment both have 3 lines), you need to Zip them together using
Table.FromColumns before expanding.The Pattern:
let // Assume Source table has "Description" and "Notes" columns with multi-line text Source = ..., // 1. Create a Custom Column that bundles the split lists into a nested table AddZippedTable = Table.AddColumn(Source, "SplitData", each Table.FromColumns( { Text.Split([Description], "#(lf)"), Text.Split([Notes], "#(lf)") }, {"Description_Split", "Notes_Split"} // New Column Names ) ), // 2. Remove original columns and Expand the new nested table RemovedOriginals = Table.RemoveColumns(AddZippedTable, {"Description", "Notes"}), Expanded = Table.ExpandTableColumn(RemovedOriginals, "SplitData", {"Description_Split", "Notes_Split"}) in ExpandedWhy this works: It treats the split lists as columns of a mini-table for each row, ensuring line 1 of Description stays with line 1 of Notes.
2. The "Regex" Alternative
Since
Web.Page (which allows JavaScript Regex) often fails in the Power BI Service due to security refresh policies, yourList.Accumulate approach is actually the safest native method!However, if you want to make it more dynamic (e.g., remove any leading number sequence like "1.", "10.", "1.2.") without listing them all, you can use
Text.PositionOfAny to find where the real text starts.Dynamic Trimmer Function:
Kod snippet'i(text as text) as text => let // Define what constitutes "real text" (e.g., A-Z) RealTextMarkers = {"a".."z", "A".."Z", "("}, // Find the position of the first real character FirstCharPosition = Text.PositionOfAny(text, RealTextMarkers), // Slice the text from that position Result = if FirstCharPosition > 0 then Text.Range(text, FirstCharPosition) else text in ResultGreat work on the parameterized folder structure, that is a solid architecture for scalability!
If this helps optimize your template workflow, I'd appreciate a Kudos! ✅
This response was assisted by AI for translation and formatting purposes.
Hi ,This is a fantastic showcase of how to dynamically switch between templates using Parameters! Using List.Accumulate to clean up bullets is also a very creative "native M" approach.
I noticed you mentioned two specific pain points: simulating Regex and handling row shifts when splitting multiple columns by new lines.
Here are two "Super User" patterns that might solve those last 20% of manual work for you.
1. Solving the "Row Shift" Issue (Multi-Column Split)
You mentioned that splitting text by lines for more than 1 column causes shifts. This happens because standard splitting creates a Cartesian product (multiplication of rows).
To keep lines aligned across multiple columns (e.g., Description and Comment both have 3 lines), you need to Zip them together using Table.FromColumns before expanding.
The Pattern:
let
// Assume Source table has "Description" and "Notes" columns with multi-line text
Source = ...,
// 1. Create a Custom Column that bundles the split lists into a nested table
AddZippedTable = Table.AddColumn(Source, "SplitData", each
Table.FromColumns(
{
Text.Split([Description], "#(lf)"),
Text.Split([Notes], "#(lf)")
},
{"Description_Split", "Notes_Split"} // New Column Names
)
),
// 2. Remove original columns and Expand the new nested table
RemovedOriginals = Table.RemoveColumns(AddZippedTable, {"Description", "Notes"}),
Expanded = Table.ExpandTableColumn(RemovedOriginals, "SplitData", {"Description_Split", "Notes_Split"})
in
ExpandedWhy this works: It treats the split lists as columns of a mini-table for each row, ensuring line 1 of Description stays with line 1 of Notes.
2. The "Regex" Alternative
Since Web.Page (which allows JavaScript Regex) often fails in the Power BI Service due to security refresh policies, your List.Accumulate approach is actually the safest native method!
However, if you want to make it more dynamic (e.g., remove any leading number sequence like "1.", "10.", "1.2.") without listing them all, you can use Text.PositionOfAny to find where the real text starts.
Dynamic Trimmer Function:
(text as text) as text =>
let
// Define what constitutes "real text" (e.g., A-Z)
RealTextMarkers = {"a".."z", "A".."Z", "("},
// Find the position of the first real character
FirstCharPosition = Text.PositionOfAny(text, RealTextMarkers),
// Slice the text from that position
Result = if FirstCharPosition > 0 then Text.Range(text, FirstCharPosition) else text
in
ResultGreat work on the parameterized folder structure, that is a solid architecture for scalability!
If this helps optimize your template workflow, I'd appreciate a Kudos! ✅
This response was assisted by AI for translation and formatting purposes.