Forum Discussion
Resolver IHow to transpose certain cells to column
Hi all,
I have a table with internal and external emails for a list of partners. I want to find a way with power query, to transpose the internal emails for different columns, and repeat these internal emails in each row for each partner.
As you can see in the image below, the number of internal emails can be 1, 2
I uploaded this sample here: https://drive.google.com/file/d/1tgv-u7W4Pui5tyBH-u7b_JbGZ3zHGpVa/view?usp=sharing
Here is a (crude) way of doing it in Power Query. I am sure this can be optimized.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZDBTsJAEIbfpWdiIorCrcQAESmYGg+GcJg0Y13dnSHTauDtndl6M1TZ0/dnd+fL7L/dZpfjm1E2yO7X8015tyk0LmZlMV2/aFp+Nq0jDe8x5OFYcdgDHS+UNkQtCoHPdoN+0VOLX2iiJoZ00ZLR1mHMHb2y6PSPYXb4p2Hq8aAARbKjAPlQBMW5DpSg347dQtcsUL/jdjIe6uGKa9ddzwWowljGW1Qo/mj0tKN0VLNSjLnvHpy5xwJZagt1DImWwlWxVEWioWSrQ5j65odX16OJHj4KBxYbe36wLiFgY2Ua+9s8YViBmMAr0uYd2FOvSJpfM3oFKfJ9d/Xr/7tv", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Partner ID" = _t, #"Partner Name" = _t, Country = _t, Name = _t, #"Email Address" = _t, #"Internal/External" = _t]), #"Added Custom" = Table.AddColumn(Source, "Custom", (k)=> Table.SelectRows(Source, each [Partner ID]=k[Partner ID] and [#"Internal/External"]="Internal")), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Internal", each Table.SelectColumns([Custom],{"Name","Email Address"})), #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.1", each Table.ToList([Internal],Combiner.CombineTextByDelimiter(","))), #"Extracted Values" = Table.TransformColumns(#"Added Custom2", {"Custom.1", each Text.Combine(List.Transform(_, Text.From), ","), type text}), #"Filtered Rows" = Table.SelectRows(#"Extracted Values", each ([#"Internal/External"] = "External")), #"Split Column by Delimiter" = Table.SplitColumn(#"Filtered Rows", "Custom.1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Custom.1.1", "Custom.1.2", "Custom.1.3", "Custom.1.4", "Custom.1.5", "Custom.1.6"}), #"Removed Other Columns" = Table.SelectColumns(#"Split Column by Delimiter",{"Partner ID", "Partner Name", "Country", "Name", "Email Address", "Internal/External", "Custom.1.1", "Custom.1.2", "Custom.1.3", "Custom.1.4", "Custom.1.5", "Custom.1.6"}) in #"Removed Other Columns"
4 Replies
- lbendlin
Super User
Here is a (crude) way of doing it in Power Query. I am sure this can be optimized.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZDBTsJAEIbfpWdiIorCrcQAESmYGg+GcJg0Y13dnSHTauDtndl6M1TZ0/dnd+fL7L/dZpfjm1E2yO7X8015tyk0LmZlMV2/aFp+Nq0jDe8x5OFYcdgDHS+UNkQtCoHPdoN+0VOLX2iiJoZ00ZLR1mHMHb2y6PSPYXb4p2Hq8aAARbKjAPlQBMW5DpSg347dQtcsUL/jdjIe6uGKa9ddzwWowljGW1Qo/mj0tKN0VLNSjLnvHpy5xwJZagt1DImWwlWxVEWioWSrQ5j65odX16OJHj4KBxYbe36wLiFgY2Ua+9s8YViBmMAr0uYd2FOvSJpfM3oFKfJ9d/Xr/7tv", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Partner ID" = _t, #"Partner Name" = _t, Country = _t, Name = _t, #"Email Address" = _t, #"Internal/External" = _t]), #"Added Custom" = Table.AddColumn(Source, "Custom", (k)=> Table.SelectRows(Source, each [Partner ID]=k[Partner ID] and [#"Internal/External"]="Internal")), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Internal", each Table.SelectColumns([Custom],{"Name","Email Address"})), #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.1", each Table.ToList([Internal],Combiner.CombineTextByDelimiter(","))), #"Extracted Values" = Table.TransformColumns(#"Added Custom2", {"Custom.1", each Text.Combine(List.Transform(_, Text.From), ","), type text}), #"Filtered Rows" = Table.SelectRows(#"Extracted Values", each ([#"Internal/External"] = "External")), #"Split Column by Delimiter" = Table.SplitColumn(#"Filtered Rows", "Custom.1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Custom.1.1", "Custom.1.2", "Custom.1.3", "Custom.1.4", "Custom.1.5", "Custom.1.6"}), #"Removed Other Columns" = Table.SelectColumns(#"Split Column by Delimiter",{"Partner ID", "Partner Name", "Country", "Name", "Email Address", "Internal/External", "Custom.1.1", "Custom.1.2", "Custom.1.3", "Custom.1.4", "Custom.1.5", "Custom.1.6"}) in #"Removed Other Columns"
CTozziHi lbendlin , first of all, thank you for your reply and sorry for the late feedback. Your solution is interesting but unfortunately, it didn't work well, and I believe it is because of the number of rows in my Data Model. I need to create the file in that format to upload in an internal system. Thank you anyway.
- lbendlin
Can you quantify what "didn't work well" means?
- lbendlin
First of all - why?!? Pivoting data should be left to the visuals, it should never be done in a data model.
Please confirm the data for A13, A14, A15 - is this a typo and should all say 23459?
So for each external email address you want to list all internal emails?