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.   places A001_Mango A011_orange A201_grapes Type ID Texas Available Available   Oragnic fruit A0001 V...
Need Help
Show and Tell
Tips and Tricks
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

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        The column names contains like

        month 1 - AMA00241_mangoes_raw, AMA01234_banana_raw,AMA04545_apples_raw, Type,ID,places

        month2 - AMA03432_mangoes_ft, AMA01434_banana_ft,AMA04535_apples_ft, AMA09095_grapes_ft,AMA0005_pear_ft,Type,ID,places

        month3 - AMA03345_peach_raw, AMA00034_orange_ft,Type,ID,places

        The dynamic cloumn name contains like AMA0%