Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Stitching a matrix in one row with duplicate values

I have below matrix table in Power bi which I am able to stitch as one. But there is an issue , if i have similar values in same column , I dont get the duplicates. since it is matrix , there are chances that my input matrix will have similar values and I would like to keep the duplicates . How can I do it? Explained below with an example:

Below is the input table in Power bi:


IdNameclassTestAnothervalueageMetadata1Metadata2

 

99bbb90222ww4yuyuyu
99aa22989jjj9yuyuyu
100uuu88333tt5  
100ss33977iii23  

 

On stichiting I get this:

IdValues.1Values.2Values.3Values.4Values.5Values.6Values.7Values.8Values.9Values.10

 

99bbb90222ww4yuyuyuaa22989
100uuu88333tt5  ss33977

 

so if you see, I had Metadata1 and Metadata2 values coming twice for Id 99 and I would have wanted my final table had two more columns with values of Metadata1 and Metadata2. and also values from column AnotherValue and age is missing for 2nd row. How can I fix this and get everything on stiching by Ids.

 

Here is the query for above output table in Power Bi:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc1BDsAQEIXhu8zaQmmDs4gFO7ZMxO37Rtp00YQvJL8RI4VAikopMGhgjIFzghN78YK8mJJ64px3Jg+83Ftrcv7Fh5Z5zAy9B9ZaOAa4sGV9Xe+7kEnOwVqrfGPfMN0=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Id = _t, Name = _t, class = _t, Test = _t, Anothervalue = _t, age = _t, Metadata1 = _t, Metadata2 = _t]),
#"Renamed Columns" = Table.RenameColumns(Source,{{"Name", "D0"}, {"class", "D1"}, {"Test", "D2"}, {"Anothervalue", "D3"}, {"age", "D4"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Id", Int64.Type}, {"D0", type text}, {"D1", Int64.Type}, {"D2", Int64.Type}, {"D3", type text}, {"D4", Int64.Type}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Id"}, "Attribute", "Value"),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Unpivoted Other Columns", {{"Value", type text}}, "en-US"),{"Attribute", "Value"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Values"),
#"Grouped Rows" = Table.Group(#"Merged Columns", {"Id"}, {{"Values", each Text.Combine([Values],";"), type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Grouped Rows", "Values", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Values.1", "Values.2", "Values.3", "Values.4", "Values.5", "Values.6", "Values.7", "Values.8", "Values.9", "Values.10"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Values.1", type text}, {"Values.2", type text}, {"Values.3", type text}, {"Values.4", type text}, {"Values.5", type text}, {"Values.6", type text}, {"Values.7", type text}, {"Values.8", type text}, {"Values.9", type text}, {"Values.10", type text}}),
#"Extracted Text After Delimiter" = Table.TransformColumns(#"Changed Type1", {{"Values.1", each Text.AfterDelimiter(_, ":"), type text}, {"Values.2", each Text.AfterDelimiter(_, ":"), type text}, {"Values.3", each Text.AfterDelimiter(_, ":"), type text}, {"Values.4", each Text.AfterDelimiter(_, ":"), type text}, {"Values.5", each Text.AfterDelimiter(_, ":"), type text}, {"Values.6", each Text.AfterDelimiter(_, ":"), type text}, {"Values.7", each Text.AfterDelimiter(_, ":"), type text}, {"Values.8", each Text.AfterDelimiter(_, ":"), type text}, {"Values.9", each Text.AfterDelimiter(_, ":"), type text}, {"Values.10", each Text.AfterDelimiter(_, ":"), type text}})
in
#"Extracted Text After Delimiter"

Need Help
  • v-kkf-msft's avatar
    v-kkf-msft
    5 years ago

    Hi Anonymous ,

     

    Please try the following code:

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc1BDsAQEIXhu8zaQmmDs4gFO7ZMxO37Rtp00YQvJL8RI4VAikopMGhgjIFzghN78YK8mJJ64px3Jg+83Ftrcv7Fh5Z5zAy9B9ZaOAa4sGV9Xe+7kEnOwVqrfGPfMN0=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Id = _t, Name = _t, class = _t, Test = _t, Anothervalue = _t, age = _t, Metadata1 = _t, Metadata2 = _t]),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(Source, {{"class", type text}, {"Test", type text}, {"age", type text}}, "en-US"),{"Name", "class", "Test", "Anothervalue", "age", "Metadata1", "Metadata2"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged"),
    #"Grouped Rows" = Table.Group(#"Merged Columns", {"Id"}, {{"Rows", each Text.Combine([Merged],"; "), type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Grouped Rows", "Rows", Splitter.SplitTextByDelimiter("; ", QuoteStyle.Csv), {"Rows.1", "Rows.2"}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "Rows.1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Rows.1.1", "Rows.1.2", "Rows.1.3", "Rows.1.4", "Rows.1.5", "Rows.1.6", "Rows.1.7"}),
    #"Split Column by Delimiter2" = Table.SplitColumn(#"Split Column by Delimiter1", "Rows.2", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Rows.2.1", "Rows.2.2", "Rows.2.3", "Rows.2.4", "Rows.2.5", "Rows.2.6", "Rows.2.7"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"Rows.1.1", type text}, {"Rows.1.2", Int64.Type}, {"Rows.1.3", Int64.Type}, {"Rows.1.4", type text}, {"Rows.1.5", Int64.Type}, {"Rows.1.6", type text}, {"Rows.1.7", type text}, {"Rows.2.1", type text}, {"Rows.2.2", Int64.Type}, {"Rows.2.3", Int64.Type}, {"Rows.2.4", type text}, {"Rows.2.5", Int64.Type}, {"Rows.2.6", type text}, {"Rows.2.7", type text}})
in
    #"Changed Type2"

     

     

    Referencing https://exceloffthegrid.com/power-query-combine-rows-into-a-single-cell/ 

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

    Best Regards,
    Winniz

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

1 Reply

  • v-kkf-msft's avatar
    v-kkf-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi Anonymous ,

     

    Please try the following code:

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc1BDsAQEIXhu8zaQmmDs4gFO7ZMxO37Rtp00YQvJL8RI4VAikopMGhgjIFzghN78YK8mJJ64px3Jg+83Ftrcv7Fh5Z5zAy9B9ZaOAa4sGV9Xe+7kEnOwVqrfGPfMN0=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Id = _t, Name = _t, class = _t, Test = _t, Anothervalue = _t, age = _t, Metadata1 = _t, Metadata2 = _t]),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(Source, {{"class", type text}, {"Test", type text}, {"age", type text}}, "en-US"),{"Name", "class", "Test", "Anothervalue", "age", "Metadata1", "Metadata2"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged"),
    #"Grouped Rows" = Table.Group(#"Merged Columns", {"Id"}, {{"Rows", each Text.Combine([Merged],"; "), type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Grouped Rows", "Rows", Splitter.SplitTextByDelimiter("; ", QuoteStyle.Csv), {"Rows.1", "Rows.2"}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "Rows.1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Rows.1.1", "Rows.1.2", "Rows.1.3", "Rows.1.4", "Rows.1.5", "Rows.1.6", "Rows.1.7"}),
    #"Split Column by Delimiter2" = Table.SplitColumn(#"Split Column by Delimiter1", "Rows.2", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Rows.2.1", "Rows.2.2", "Rows.2.3", "Rows.2.4", "Rows.2.5", "Rows.2.6", "Rows.2.7"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"Rows.1.1", type text}, {"Rows.1.2", Int64.Type}, {"Rows.1.3", Int64.Type}, {"Rows.1.4", type text}, {"Rows.1.5", Int64.Type}, {"Rows.1.6", type text}, {"Rows.1.7", type text}, {"Rows.2.1", type text}, {"Rows.2.2", Int64.Type}, {"Rows.2.3", Int64.Type}, {"Rows.2.4", type text}, {"Rows.2.5", Int64.Type}, {"Rows.2.6", type text}, {"Rows.2.7", type text}})
in
    #"Changed Type2"

     

     

    Referencing https://exceloffthegrid.com/power-query-combine-rows-into-a-single-cell/ 

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

    Best Regards,
    Winniz

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.