Transform into multiple rows from particular cell value

[gigotomo]

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

[gigotomo · Accepted solution]

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"

[Anonymous]

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

[PaulDBrown]

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"