Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

transformation with power query

Good day to all master, again I am requesting your valuable help.

I need to transform the attached file here file source transformation

 

so that it looks like this

 

 

Thanks in advance

 

 

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    Hi Anonymous

    In Queries Editor, 

    1.create a index column from1, 

     

    2.create a conditional column 

     

    3. fill down on [custom] column

     

    4.click on [atributo] column, select "pivot table"

     

    5.fill down in [nombre] column,

       fill up in [value] column,

       after these, click on the "down arrow" of  [type machine] column, select "remove empty"

     

    finally, you could remove useless columns.

     

    Best Regards

    Maggie

4 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Icon for Community Champion rankCommunity Champion
    7 years ago

    Anonymous

     

    Try this one

     

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
   ChangedType = Table.TransformColumnTypes(Source,{{"atributo", type text}, {"valores", type text}}),

Nombre=Table.SelectRows(ChangedType,each [atributo]="nombre")[valores],
typemachine=Table.SelectRows(ChangedType,each [atributo]="type machine")[valores],
value=Table.SelectRows(ChangedType,each [atributo]="value")[valores],
Final=Table.FromColumns({Nombre,typemachine,value},{"Nombre","type machine","value"})

in
    Final
    • Zubair_Muhammad's avatar
      Zubair_Muhammad
      Icon for Community Champion rankCommunity Champion
      7 years ago

      Anonymous

       

      If you have many distinct values in Atributo column, its better to use List.Generate function to automate the transformation

       

      Here is another way

      Please see the attached Excel file as well

       

      let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
   ChangedType = Table.TransformColumnTypes(Source,{{"atributo", type text}, {"valores", type text}}),
Mylist=List.Buffer(List.Distinct(ChangedType[atributo])),

IndividualLists=List.Generate(()=>

[Index=0,Mlist=Table.SelectRows(ChangedType,each [atributo]=Mylist{0})[valores]], 
each [Index] < List.Count(Mylist),
each [Index=[Index]+1,Mlist=Table.SelectRows(ChangedType,each [atributo]=Mylist{Index})[valores]],
each [Mlist]),
CombinedLists=List.Zip(IndividualLists),
ConvertedtoTable = Table.FromList(CombinedLists, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Custom" = Table.AddColumn(ConvertedtoTable, "Custom", each Text.Combine([Column1],",")),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Added Custom", "Custom", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), Mylist),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter",{"Column1"})
in
    #"Removed Columns"
  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    Hi Anonymous

    In Queries Editor, 

    1.create a index column from1, 

     

    2.create a conditional column 

     

    3. fill down on [custom] column

     

    4.click on [atributo] column, select "pivot table"

     

    5.fill down in [nombre] column,

       fill up in [value] column,

       after these, click on the "down arrow" of  [type machine] column, select "remove empty"

     

    finally, you could remove useless columns.

     

    Best Regards

    Maggie

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      v-juanli-msft Thank you very much for the help, this is the answer I was looking for, just brilliant.
      again thanks for coming to help me