Forum Discussion

gigotomo's avatar
gigotomo
Frequent Visitor
3 years ago
Solved

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 mult...
Need Help
  • gigotomo's avatar
    gigotomo
    3 years ago

    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"

PaulDBrown's avatar
PaulDBrown
Community Champion
3 years ago

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"