How can i count same cells and and transform after? from 1toN to 1to1 ?

[n8schicht]

Hallo everybody!

i have this list in powerquery:

powerquery - listresult - this is how it should look after the conversion.

[Ashish_Mathur · Accepted solution]

Hi,

Try this M code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ0MjJW0lFKVIrVQfCKUXgpKLw0FF46Cq8EwjM3MzUD8pJQeHmocqjcvFwUblY2mGtiampuAXJOCgq3sFwpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Number = _t, Text = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Number", Int64.Type}, {"Text", type text}}),
    Partition = Table.Group(#"Changed Type", {"Number"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Text", "Index"}, {"Text", "Index"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Expanded Partition", {{"Index", type text}}, "en-IN"), List.Distinct(Table.TransformColumnTypes(#"Expanded Partition", {{"Index", type text}}, "en-IN")[Index]), "Index", "Text")
in
    #"Pivoted Column"

Hope this helps.

[Zubair_Muhammad]

@n8schicht 

You can do like this.

Please attached file's query editor for steps

Basically first Group by number column and get corresponding Text as List. Then we can split this text into separate columns

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ0MjJW0lHyyS9KzVXwLCguzTVUitXBKmGES8IYl4QJRMLczNQMVcIcl4QFLglLXBKGBjhlgD6JBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Number = _t, Text = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Number", Int64.Type}, {"Text", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Number"}, {{"Text", each _[Text], type list}}),
    #"Extracted Values" = Table.TransformColumns(#"Grouped Rows", {"Text", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Text", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Text.1", "Text.2", "Text.3", "Text.4", "Text.5"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Text.1", type text}, {"Text.2", type text}, {"Text.3", type text}, {"Text.4", type text}, {"Text.5", type text}})
in
    #"Changed Type1"

[Greg_Deckler]

Seems like that would be the reverse of an unpivot, which would make it a pivot. You can do that from the UI in Query Editor.