Forum Discussion
Transform into multiple rows from particular cell value
I have the below table.
| Market | Date | Value |
| Spain | 8/26/2022 | 25 |
| UK | 8/26/2022 | 23 |
| Italy | 8/26/2022 | 29 |
| All | 8/26/2022 | 19 |
Whenever the Market is "All" i wish to create multiple rows like below using power query
| Market | Market_1 | Date | Value |
| Spain | Spain | 8/26/2022 | 25 |
| UK | UK | 8/26/2022 | 23 |
| Italy | Italy | 8/26/2022 | 29 |
| All | Spain | 8/26/2022 | 19 |
| All | UK | 8/26/2022 | 19 |
| All | Italy | 8/26/2022 | 19 |
Such that "Spain" remains "Spain", "UK" remains "UK" but "All" gets transformed into multiple rows which has "Spain", "Italy" & "UK" in the Market_1 column
I found a simpler solution using "Split Column" (into Rows).
I created a conditional column saying if market is "All" then "abc" else the "market"
Then i used split column (into rows) so I get 3 rows wth the market "All"
then i created the final "Market1" column based on the values
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Market", type text}, {"Date", type text}, {"Value", Int64.Type}}),
#"Added Conditional Column" = Table.AddColumn(#"Changed Type", "DummY_market", each if [Market] = "All" then "abc" else null),
#"Split Column by Position" = Table.ExpandListColumn(Table.TransformColumns(#"Added Conditional Column", {{"DummY_market", Splitter.SplitTextByRepeatedLengths(1), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "DummY_market"),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"DummY_market", type text}}),
#"Added Conditional Column1" = Table.AddColumn(#"Changed Type1", "Market1", each if [DummY_market] = null then [Market] else if [DummY_market] = "a" then "Spain" else if [DummY_market] = "b" then "UK" else if [DummY_market] = "c" then "IT" else null),
#"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column1",{"DummY_market"})
in
#"Removed Columns"
3 Replies
I found a simpler solution using "Split Column" (into Rows).
I created a conditional column saying if market is "All" then "abc" else the "market"
Then i used split column (into rows) so I get 3 rows wth the market "All"
then i created the final "Market1" column based on the values
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Market", type text}, {"Date", type text}, {"Value", Int64.Type}}),
#"Added Conditional Column" = Table.AddColumn(#"Changed Type", "DummY_market", each if [Market] = "All" then "abc" else null),
#"Split Column by Position" = Table.ExpandListColumn(Table.TransformColumns(#"Added Conditional Column", {{"DummY_market", Splitter.SplitTextByRepeatedLengths(1), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "DummY_market"),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"DummY_market", type text}}),
#"Added Conditional Column1" = Table.AddColumn(#"Changed Type1", "Market1", each if [DummY_market] = null then [Market] else if [DummY_market] = "a" then "Spain" else if [DummY_market] = "b" then "UK" else if [DummY_market] = "c" then "IT" else null),
#"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column1",{"DummY_market"})
in
#"Removed Columns"See if this works
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi5IzMxT0lEyMtM3sNA3MjAyAnFMlWJ1opVCvTEkjMESniWJOZUYcpZgOcecHHQZQ6BMLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Market = _t, Date = _t, Value = _t]), Country = Source, #"Removed Other Columns" = Table.SelectColumns(Country,{"Market"}), Countrylist = Source, #"Filtered Rows" = Table.SelectRows(Countrylist, each ([Market] = "All")), #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each Country), #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Market"}, {"Custom.Market"}), #"Renamed Columns" = Table.RenameColumns(#"Expanded Custom",{{"Custom.Market", "Market 1"}}), Source1 = Source, #"Filtered Rows1" = Table.SelectRows(Source1, each ([Market] <> "All")), #"Added Custom1" = Table.AddColumn(#"Filtered Rows1", "Market 1", each [Market]), #"Appended Query" = Table.Combine({#"Added Custom1", #"Renamed Columns"}), #"Changed Type" = Table.TransformColumnTypes(#"Appended Query",{{"Date", type date}, {"Value", Int64.Type}}), #"Filtered Rows2" = Table.SelectRows(#"Changed Type", each ([Market 1] <> "All")) in #"Filtered Rows2"Hi gigotomo,
Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.
If these also don't help, please share more detailed information to help us clarify your scenario to test.
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
