Forum Discussion

Dennis154's avatar
Dennis154
New Member
8 years ago
Solved

Power-Query - Foreach or any other

Hi,

 

this is a produced Example to show the problem.

 

i have a Table named: Car_each_Country like this:

Car          Deutschland    Frankreich   Russland

VW               x                       x              x

Porsche        x                       x

Lada                                                     x

Renault                                 x

 

then i have a Table Countrys:

Land

Deutschland

Frankreich Russland

 

the Result should be as follows:

Car            Country

VW            Deutschland

Porsche     Deutschland

VW            Frankreich

Porsche     Frankreich

Renault      Frankreich

VW            Russland

Lada          Russland

 

as you can see, it is a Table which shows where have "Car_each_Country" the "x"

 

My Test solution for approach is the following:

let
   #"countrylist" = Table.ToList(Countrys,null),  
   #"selected Rows" = Table.SelectRows(Car_each_Country,each (Record.Field(_,#"countrylist"{0})="x")),
   #"selected Rows addCountry" = Table.AddColumn(#"selected Rows","Country", each #"countrylist"{0}),
  
   //nächste Tabelle mit anderem Land
   #"selected Rows2" = Table.SelectRows(Car_each_Country,each (Record.Field(_,#"countrylist"{1})="x")),
   #"selected Rows2 addCountry" = Table.AddColumn(#"selected Rows2","Country", each #"countrylist"{1}),
  
   //nächstes Land
   #"selected Rows3" = Table.SelectRows(Car_each_Country,each (Record.Field(_,#"countrylist"{2})="x")),
   #"selected Rows3 addCountry" = Table.AddColumn(#"selected Rows3","Country", each #"countrylist"{2}),

   // combinetables
   #"outTable" = Table.Combine({#"selected Rows addCountry",#"selected Rows2 addCountry",#"selected Rows3 addCountry"})

in
#"outTable"

 

is there any other option to accomplish that, so i have not to hardcode it for every "Country"

 

In Real i have many Groups n Roles....

 

Thank you for answer

 

 

 

 

  • Greg_Deckler's avatar
    Greg_Deckler
    8 years ago

    I think that you may be overcomplicating this one. You should just need your first table:

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCgtX0lGqgONYnWilgPyi4oxUJGGwqE9iSiKIDUYQhUGpeYmlOSUwIZDCWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Car = _t, Deutschland = _t, Frankreich = _t, Russland = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Car", type text}, {"Deutschland", type text}, {"Frankreich", type text}, {"Russland", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Car"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Columns", each ([Value] = "x")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Value"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Attribute", "Country"}})
in
    #"Renamed Columns"

3 Replies

  • Dennis154's avatar
    Dennis154
    New Member
    8 years ago

    Hi,

     

    this is a produced Example to show the problem. 

     

    i have a Table named: Car_each_Country like this:


    Car          Deutschland    Frankreich   Russland
    VW               x                       x              x
    Porsche        x                       x
    Lada                                                     x
    Renault                                 x

     

     

    then i have a Table Countrys:


    Land
    Deutschland
    Frankreich

    Russland 

     

    the Result should be as follows:


    Car            Country
    VW            Deutschland
    Porsche     Deutschland
    VW            Frankreich
    Porsche     Frankreich
    Renault      Frankreich
    VW            Russland
    Lada          Russland

     

     

    as you can see, it is a Table which shows where have "Car_each_Country" the "x"

     

     

    My Test solution for approach is the following:


    let
       #"countrylist" = Table.ToList(Countrys,null),  
       #"selected Rows" = Table.SelectRows(Car_each_Country,each (Record.Field(_,#"countrylist"{0})="x")),
       #"selected Rows addCountry" = Table.AddColumn(#"selected Rows","Country", each #"countrylist"{0}),
      
       //nächste Tabelle mit anderem Land
       #"selected Rows2" = Table.SelectRows(Car_each_Country,each (Record.Field(_,#"countrylist"{1})="x")),
       #"selected Rows2 addCountry" = Table.AddColumn(#"selected Rows2","Country", each #"countrylist"{1}),
      
       //nächstes Land
       #"selected Rows3" = Table.SelectRows(Car_each_Country,each (Record.Field(_,#"countrylist"{2})="x")),
       #"selected Rows3 addCountry" = Table.AddColumn(#"selected Rows3","Country", each #"countrylist"{2}),

       // combinetables
       #"outTable" = Table.Combine({#"selected Rows addCountry",#"selected Rows2 addCountry",#"selected Rows3 addCountry"})

    in
    #"outTable"

     


    is there any other option to accomplish that, so i have not to hardcode it for every "Country" ?

     

    In Real i have many Groups n Roles....

     

     

    Thank you for answer

     

     

     

     

     

     

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    8 years ago

    I think that you may be overcomplicating this one. You should just need your first table:

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCgtX0lGqgONYnWilgPyi4oxUJGGwqE9iSiKIDUYQhUGpeYmlOSUwIZDCWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Car = _t, Deutschland = _t, Frankreich = _t, Russland = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Car", type text}, {"Deutschland", type text}, {"Frankreich", type text}, {"Russland", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Car"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Columns", each ([Value] = "x")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Value"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Attribute", "Country"}})
in
    #"Renamed Columns"
    • Dennis154's avatar
      Dennis154
      New Member
      8 years ago

      Hi,

       

      you're so wonderfull, i love your answer.

       

      many many thanks for answering..