create 0 observations from multiple rows by column name

[Anonymous]

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
Virginia	Available	Available		Oragnic fruit	A0001
MD	Available		Available	Oragnic fruit	A0001
DC	Available	Available		Oragnic fruit	A0001
VA	Available	Available	Available	Oragnic fruit	A0001
CA	Available	Available		Regular Fruit	B0001
NJ	Available	Available		Regular Fruit	B0001
NY	Available		Available	Regular Fruit	B0001
DE	Available	Available		Regular Fruit	B0001
PA	Available	Available	Available	Regular Fruit	B0001

Expected Results:

A001_Mango	A011_orange	A201_grapes	Type	ID
Available	Available	Available	Oragnic fruit	A0001
Available	Available	Available	Regular Fruit	B0001

[v-frfei-msft]

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]

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]

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

[Anonymous]

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%