Forum Discussion
Split string after every n'th character multiple times
Hello experts
After many hours i finally succeded with some Power Automate flows which exports my firms Planner tasks, so i can use that export as a source for Power BI.
I just have 1 problem left....
In my export multiple employees can be assigned the same task, hence my export looks something like this:
| Task 1 | TimTomJoe |
| Task 2 | Eli |
| Task 3 | TomEli |
The Assigned names are combined in 1 cell with no delimiters.
Fortunately im not exporting their names but their initials (i just used names in the example for simplification), so all names/initials consists of only 3 letters.
My question is now. How do i split this up?
What i want is just being able to show who have been assigned the different task, and in another visual how many tasks the individual employees have been assigned.
Therefore I think so far, that I probably have to duplicate every task ID for every employee name, but im open for other ideas.
EDIT: Just to clarify. The tasks are in one column and names in another. When i hit the post button my table looks only 1 column.
ABech Try this:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkkszlYwVNJRCsnMDcnP9cpPVYrVgQobAYVdczIRAsYgdfm5YLFYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}), #"Split Column by Position" = Table.SplitColumn(#"Changed Type", "Column2", Splitter.SplitTextByRepeatedLengths(3), {"Column2.1", "Column2.2", "Column2.3"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"Column2.1", type text}, {"Column2.2", type text}, {"Column2.3", type text}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Column1"}, "Attribute", "Value"), #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}) in #"Removed Columns"
3 Replies
ABech Try this:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkkszlYwVNJRCsnMDcnP9cpPVYrVgQobAYVdczIRAsYgdfm5YLFYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}), #"Split Column by Position" = Table.SplitColumn(#"Changed Type", "Column2", Splitter.SplitTextByRepeatedLengths(3), {"Column2.1", "Column2.2", "Column2.3"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"Column2.1", type text}, {"Column2.2", type text}, {"Column2.3", type text}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Column1"}, "Attribute", "Value"), #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}) in #"Removed Columns"Hello Greg_Deckler
And thank you for your help once more.
I'm quite a newbie at Power BI, and do not have much experience with DAX or transforming data.
Can you tell me where I should insert this code?
ABech Use the Transform data button on the Home tab to get to Power Query Editor. Create a new Blank query. Open Advanced Editor, replace the entire contents with the code provided.
