Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hey
This is a post derived from another post raised by rob1123 a while ago
In my case, I have a Col5 that is a list of records and I would like to extracts the values from the LAB field and concatenate the values with a comma as separator to get at the end a table as below
Col1 | Col2 | Col3 | Col5 |
A | B | C | B3, T1, TK |
X | Y | Z |
Below is code from rob1123 that was answered by MarcelBeug in which I have inserted List3 and List4
let
List3 = {[ID=21, LAB="B3"],[ID=22,LAB="T1"],[ID=23,LAB="TK"]},
List4 = {[ID=20, LAB=""]},
List1 = {78876491678679, 12, [SomeNumber = 13, SomeText = "MarcelBeug"]},
List2 = {77378856782687, 23, null},
Source = #table({"Col1","Col2","Col3","Col4","Col5"},
{{"A","B","C",List1,List3},
{"X","Y","Z",List2,List4}}),
RecordToList = Table.TransformColumns(Source,{{"Col4", each List.Combine({List.FirstN(_,2), if _{2} = null then {} else Record.ToList(_{2})})}}),
#"Extracted Values1" = Table.TransformColumns(RecordToList, {"Col4", each Text.Combine(List.Transform(_, Text.From), ":"), type text})
in
#"Extracted Values1"
I tried for a while but I have no idea how to proceed. I suppose I need to
- chain several TransformColumns with ListOfRecords -> TableOfLists and to combine the rows of LAB column ?
- OR to mimic https://www.youtube.com/watch?v=nJ7LzwiSwnw doing some Expands then GroupBy using "," as separator
- OR?
I think I need some help here...
Solved! Go to Solution.
Use this function on each row passing it the column with a list of records (the ones with LAB as one of their fields):
(listOfRecords as list) as nullable text =>
let
RecordsWithoutNullsAndEmptyStrings =
List.Select(
listOfRecords,
each not List.Contains({null, ""}, [LAB])
),
ConcatenatedValues =
List.Accumulate(
RecordsWithoutNullsAndEmptyStrings,
null,
(prevVal as nullable text, record_ as record) as nullable text =>
if prevVal is null then
record_[LAB]
else
prevVal & ", " & record_[LAB]
)
in
ConcatenatedValues
Best
D
Perhaps it meant creating a Manual function and invoking the function from the Power Query editor. I'm going to try this tomorrow morning!! Thank you for your support
Use this function on each row passing it the column with a list of records (the ones with LAB as one of their fields):
(listOfRecords as list) as nullable text =>
let
RecordsWithoutNullsAndEmptyStrings =
List.Select(
listOfRecords,
each not List.Contains({null, ""}, [LAB])
),
ConcatenatedValues =
List.Accumulate(
RecordsWithoutNullsAndEmptyStrings,
null,
(prevVal as nullable text, record_ as record) as nullable text =>
if prevVal is null then
record_[LAB]
else
prevVal & ", " & record_[LAB]
)
in
ConcatenatedValues
Best
D
Thanks a lot for your time and response darlove but I think I still need your help to understand where to insert the function in my example and his to call it.
Hey
darlove as provided a good solution, thanks to him.
I found another solution that I wanted to share here that is based on basic operations
This is based on Group By & Add Column
1/ export a 2nd time the data (my example was an abstract of my real table)
Let's assume in my example Col1 contains an unique key
I export here only Col1 and Col5 where Col5 contains the list of records
2/ in this export:
a) Expand the list and the records with one row per record
b) Group By, Col1 then new column = AllData, All row
c) Add column, Custom Column Col6 defined as Col6 = [AllData][LAB]
d) Expand the new column, use "," as separator
3/ As this 2nd export uses same key than the 1st one, you can simply use RELATED to get the Col6
Perhaps it meant creating a Manual function and invoking the function from the Power Query editor. I'm going to try this tomorrow morning!! Thank you for your support
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |