Expand lists and their values into several or a single column

[Denudi]

Hey guys,

I'm already trying for a long time, used microsoft learn and chatGPT and it feels like almost every possible function I found - now I'm here and hope you can help. 

My initial situation and aim:

I want to transform the Column "Mitigation Details", so first of all the useless dashes disappear and I can sperate every action to work with these data further on. I solved this easily by adding a costum column and the following code:

let 
inputText = [Mitigation Details], 
delimiter = " - - - - -", 
beforeDelimiter = Text.BeforeDelimiter(inputText, delimiter), 
extractedList = Text.Split(beforeDelimiter, "Action"), 
resultList = List.RemoveFirstN(extractedList, 1) 
in resultList

The result is of course a new column with the created list objects:

If I go into a single List, the result is good and as desired so far, I could work with that:

But the problem is, that I neither can visualize these "List" objects in that form, nor can I make further calculations or transformations. So my goal (and current problem) is, that I'm trying to expand all of the values in the lists into separate columns (1 row = 1 column => one list contains 4 "Actions" -> 4 columns) or option 2 is, to write each list with its values below each other in a single column within a new table and provide each line with its associated id (there is an ID column in that source table) in order to be able to assign it to the original record. Option 2 is the better way for me I think.

I tried a lot with table.expandListColumn, table.Pivot und table.AddColumn in various combinations, but either I get a circular reference or a stack overflow error. Somehow I have the feeling, that the issue could also be, that I put everything into that one function of the new costum column.. maybe I have to split the steps? However, I would appreciate your help a lot! 🙂 

kind regards

[BA_Pete]

Hi @Denudi ,

Can you provide an example of your original data in a copyable format please?

Pete

[Denudi]

Hey @BA_Pete, sure, I hope that will do it:

ID	Mitigation Details
97730	Action - 105848 -  Qualitätsprüfung der - Open - 28/02/2023 - Undefined - Undefined Action - 105849 - Massen- und Mengen - Grobkalkulation- Open - 30/09/2023 - Undefined - Undefined Action - 105850 -  Überarbeitung der Entwurf-  - Open - 30/09/2023 - Undefined - Undefined  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -
98946	Action - 107464 - Qualitätspr - Open - 01/02/2023 - Undefined - Undefined Action - 107465 - Massen- und Me - Open - 30/09/2023 - Undefined - Undefined Action - 107466 - Überarbeitung der Entwu - Open - 30/09/2023 - Undefined - Undefined  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -
98946	Action - 107464 - Qualitätsprüfung der Entw - Open - 01/02/2023 - Undefined - Undefined Action - 107465 - Massen- und Mengenermitt - Open - 30/09/2023 - Undefined - Undefined Action - 107466 - Überarbeitung der E - Open - 30/09/2023 - Undefined - Undefined  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -
98946	Action - 93863 - Mitigation Ac - Open - 31/12/2032 - Undefined - Undefined  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  -
98947	Action - 79792 - Unterstüt - 30/04/2022 - Undefined - Undefined Action - 93174 - Identifizierung und - No more relevant - 31/08/2022 - Undefined - Undefined Action - 105873 - Restliches - Open - 31/12/2023 - Undefined - Undefined Action - 107228 - Wechsel auf Top - Closed (succesful) - 01/12/2022 - Undefined - Undefined Action - 108653 - Halle-Saale-Wor - Closed (succesful) - 30/11/2022 - Undefined - Undefined  -  -  -  -  -  -  -

[BA_Pete]

Would something like this work for you?

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jY7NasMwEIRfRfjUQoP1Y8fysfSSXBJoGnxwcxDyOhGoK2NZPfTps6EkBEqJYdmdGdiPadus1nVRZS/Zq51cQLZgtRJVQXfdAU6udz8OxoRHlrCjdBPYVxiBjeDh2+BEkRI517nkUpLZYwe9Q+ju9Sfe6IKXulIk3iFO3tkTRDLbAfCXJOSFpGaRKik1iQbsKYJnJvXsIwyUvPkQ6e8pJmsh9sk/U8iv8Hk19bK8tFgZ72GxM4Z2E8b/4IrnQjyAk/s72eFwBg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"Mitigation Details" = _t]),

    addSplitList = Table.AddColumn(Source, "splitList", each Text.Split([Mitigation Details], "#(lf)")),
    expandSplitList = Table.ExpandListColumn(addSplitList, "splitList")
    
in
    expandSplitList

Gives the following output:

If you wanted to subsequently break it out into new columns, you could filter out the "- - - -" row and split the column on " - " as a delimiter.

Pete

[BA_Pete]

For clarity, the code I provided is a complete example query so you can see the solution working in Power Query, it is not intended to be added as-is into an existing query.

To make this work on an existing query, you only need to add a custom column with this calculation:

Text.Split([Mitigation Details], "#(lf)")

Then expand the resulting list column to NEW ROWS using this button:

Pete

[Denudi]

Hey Pete, thanks a lot for your suggestions!
Sadly, the result of your code are tables, which can not be visualized or furthermore transformed easily. 
The split method works fine and also results in lists, whose values are good to use, but the Power BI function "Expand to New Rows" results again in a single column, and not several, and the content of every row is only the first element of the list. Is that an error in my application? I can't imagine, that this is usually not the result of "Expand to New Rows", otherwise it would say "Expand to New Row" 😅

Dennis 

[BA_Pete]

It sounds like either an error in application or non-representative example data.

Did you try my example query to see if that works how you want it to?

Copy all of the following code, create a new blank query in Power Query then, in Advanced Editor for your new query, paste the whole lot of it in there replacing the default code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jY7NasMwEIRfRfjUQoP1Y8fysfSSXBJoGnxwcxDyOhGoK2NZPfTps6EkBEqJYdmdGdiPadus1nVRZS/Zq51cQLZgtRJVQXfdAU6udz8OxoRHlrCjdBPYVxiBjeDh2+BEkRI517nkUpLZYwe9Q+ju9Sfe6IKXulIk3iFO3tkTRDLbAfCXJOSFpGaRKik1iQbsKYJnJvXsIwyUvPkQ6e8pJmsh9sk/U8iv8Hk19bK8tFgZ72GxM4Z2E8b/4IrnQjyAk/s72eFwBg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"Mitigation Details" = _t]),

    addSplitList = Table.AddColumn(Source, "splitList", each Text.Split([Mitigation Details], "#(lf)")),
    expandSplitList = Table.ExpandListColumn(addSplitList, "splitList")
    
in
    expandSplitList

If this works how you want it to but you're not getting the same result in your actual query, then I'll need to have a look in more detail at your actual query to understand where the issue lies.

If this example doesn't do what you want it to, then you'll need to provide me with clearer expected output examples, showing exactly how you want the data to end up after the whole process.

Pete