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                   ...
  • 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"
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