Forum Discussion
Expand lists and their values into several or a single column
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 - - - - - - - |
Super User
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
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 expandSplitListIf 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