Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

create 0 observations from multiple rows by column name

i have a table with data as below :

Please help how can i do this scenario.

 

placesA001_MangoA011_orangeA201_grapesTypeID
TexasAvailableAvailable Oragnic fruitA0001
VirginiaAvailableAvailable Oragnic fruitA0001
MDAvailable AvailableOragnic fruitA0001
DCAvailableAvailable Oragnic fruitA0001
VAAvailableAvailableAvailableOragnic fruitA0001
CAAvailableAvailable Regular FruitB0001
NJAvailableAvailable Regular FruitB0001
NYAvailable AvailableRegular FruitB0001
DEAvailableAvailable Regular FruitB0001
PAAvailableAvailableAvailableRegular FruitB0001

Expected Results:

 

A001_MangoA011_orangeA201_grapesTypeID
AvailableAvailableAvailableOragnic fruitA0001
AvailableAvailableAvailableRegular FruitB0001

 

 

 

Need Help
Show and Tell
Tips and Tricks

4 Replies

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support
    6 years ago

    Hi Anonymous ,

     

    Does that meet your requirement?

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkmtSCxW0lFyLEvMzElMyklFYysAsX9RYnpeZrJCWlFpZglI3sDAwFApVidaKSyzKD0zLzORbAN8XbAoR+bj1uriTL6zHfFoJc52Z3xGgGwPSk0vzUksUnCDanWCa/XzIl9rJIHgwq3VxZVsWwOIDS4cRsQCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [places = _t, A001_Mango = _t, A011_orange = _t, A201_grapes = _t, Type = _t, ID = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"places", type text}, {"A001_Mango", type text}, {"A011_orange", type text}, {"A201_grapes", type text}, {"Type", type text}, {"ID", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([A011_orange] = "Available") and ([A201_grapes] = "Available")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"places"})
in
    #"Removed Columns"

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thank you for the response. 

       

      Actually my column names are dynamic. every month the column names changes ID,Type and places columns remain same. the other starts with A0% columns names and count of A0% columns changes.

       

      so far my code is as below

       

      let
      Source = Excel.Workbook(File.Contents("C:\fruits.xlsx"), null, true),
      #"Added Custom" = Table.AddColumn(Source, "PromoteHeader", each Table.PromoteHeaders([Data])),
      #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Kind] = "Sheet") and ([Item] = "Fruits")),
      #"Removed Columns" = Table.SelectColumns(#"Filtered Rows",{"PromoteHeader"}),
      DistinctColumn = List.Union(List.Transform(Table.Column(#"Removed Columns",Table.ColumnNames(#"Removed Columns"){0}), each Table.ColumnNames(_))),
      ExpandTable =Table.ExpandTableColumn(#"Removed Columns","PromoteHeader",DistinctColumn,DistinctColumn),
      #"Removed Columns1" = Table.RemoveColumns(ExpandTable,{"places"}),
      #"Filtered Rows1" = Table.SelectRows(#"Removed Columns1", each ([ID] = "A0001" or [ID] = "B0001"))

      in
      #"Filtered Rows1"

      • edhans's avatar
        edhans
        Community Champion
        6 years ago

        Can you give examples of several months (3+) of column names? This can be fully dynamic, but we need to see the pattern