Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi, I am trying to group a list of values in a column for every 100 rows then join them with a comma as seperator into a single value. Can anyone assist with a way to do this in Power Query please?
Example:
=TEXTJOIN(",",TRUE,A2:A101)
=TEXTJOIN(",",TRUE,A102:A201)
=TEXTJOIN(",",TRUE,A202:A205)
etc
The end result in Power Query should look like column C below. There is no sorting required, it just needs to concaenate the first 100 vaues then the next 100 and so on.
Solved! Go to Solution.
Hi @Trebor84 ,
you can do this by:
Please check out this code and follow the steps:
let
Source = Table.FromRows(
Json.Document(
Binary.Decompress(
Binary.FromText(
"PdO7bR0wAATBXhQ7eCTv+KlFUP9tGIahyTbbaL6/v8Zc6dfPn/+11VFXvd/qRw011VJRHvWoRz3qsT22x/bYHttje2yP7bE9tsfxOB7H43gcj+NxPI7H8Tge1+N6XI/rcT2ux/W4HtfjejyP5/E8nsfzeB7P43k8j/f7yOejhppqqaiqrY66ymN4DI/hMTyGx/AYHsNjeAyP6TE9psf0mB7TY3pMj+kxPZbH8lgey2N5LI/lsTyWx/KIRzziEY94cB7Ow3k4D+fhPJyH83AezsN5OA/n4Tych/NwHs7DeTgP5+E8nIfzcB7Ow3k4D+fhPJyH83AezsN5OA/n4Tych/NwHs7DeTgP5+E8nIfzcB7Ow3k4D+fhPJyX83Jezst5OS/n5bycl/NyXs7LeTkv5+W8nJfzcl7Oy3k5L+flvJyX83Jezst5OS/n5bycl/NyXs7LeTkv5+W8nJfzcl7Oy3k5L+flvJyX83Jezst5OS/n5bycl/NyXs7LeTkv5+W8nJfzcl7Oy3k5L+flvP+c//wF",
BinaryEncoding.Base64
),
Compression.Deflate
)
),
let
_t = ((type nullable text) meta [Serialized.Text = true])
in
type table [VALUE = _t]
),
#"Changed Type" = Table.TransformColumnTypes(Source, {{"VALUE", type text}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
#"Integer-Divided Column" = Table.TransformColumns(
#"Added Index",
{{"Index", each Number.IntegerDivide(_, 100), Int64.Type}}
),
#"Grouped Rows" = Table.Group(
#"Integer-Divided Column",
{"Index"},
{{"Count", each Text.Combine(_[VALUE], ", ")}}
)
in
#"Grouped Rows"
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
NewStep=List.Transform(Table.Split(Table.TransformColumns(PreviousStepName,{},Text.From),100),each Text.Combine([Value],","))
Thanks all, these work perfectly
NewStep=List.Transform(Table.Split(Table.TransformColumns(PreviousStepName,{},Text.From),100),each Text.Combine([Value],","))
Hi @Trebor84 ,
you can do this by:
Please check out this code and follow the steps:
let
Source = Table.FromRows(
Json.Document(
Binary.Decompress(
Binary.FromText(
"PdO7bR0wAATBXhQ7eCTv+KlFUP9tGIahyTbbaL6/v8Zc6dfPn/+11VFXvd/qRw011VJRHvWoRz3qsT22x/bYHttje2yP7bE9tsfxOB7H43gcj+NxPI7H8Tge1+N6XI/rcT2ux/W4HtfjejyP5/E8nsfzeB7P43k8j/f7yOejhppqqaiqrY66ymN4DI/hMTyGx/AYHsNjeAyP6TE9psf0mB7TY3pMj+kxPZbH8lgey2N5LI/lsTyWx/KIRzziEY94cB7Ow3k4D+fhPJyH83AezsN5OA/n4Tych/NwHs7DeTgP5+E8nIfzcB7Ow3k4D+fhPJyH83AezsN5OA/n4Tych/NwHs7DeTgP5+E8nIfzcB7Ow3k4D+fhPJyX83Jezst5OS/n5bycl/NyXs7LeTkv5+W8nJfzcl7Oy3k5L+flvJyX83Jezst5OS/n5bycl/NyXs7LeTkv5+W8nJfzcl7Oy3k5L+flvJyX83Jezst5OS/n5bycl/NyXs7LeTkv5+W8nJfzcl7Oy3k5L+flvP+c//wF",
BinaryEncoding.Base64
),
Compression.Deflate
)
),
let
_t = ((type nullable text) meta [Serialized.Text = true])
in
type table [VALUE = _t]
),
#"Changed Type" = Table.TransformColumnTypes(Source, {{"VALUE", type text}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
#"Integer-Divided Column" = Table.TransformColumns(
#"Added Index",
{{"Index", each Number.IntegerDivide(_, 100), Int64.Type}}
),
#"Grouped Rows" = Table.Group(
#"Integer-Divided Column",
{"Index"},
{{"Count", each Text.Combine(_[VALUE], ", ")}}
)
in
#"Grouped Rows"
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Table.Group() does the trick at one go based on a 0-based index column,
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TdG5DcIAEETRXhwTMIc5QqAMy3L/XYCEtN/Zz552dtuW1/tzaNkv//JUpjq1Tt2m7lOPqeeUriSKYIQjICEJSlgCE5rRfLoJzWhGM5rRjGY0owUtaDlNiBa0oAUtaEELWtGKVrSePoZWtKIVrWhFW3/a/gU=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Value = _t]),
#"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
Grouped = Table.Group(#"Added Index", "Index", {"Grouped", each _}, 0, (x,y) => Byte.From(Number.Mod(y,10)=0))
in
Grouped
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |