transformation with power query

[Anonymous]

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

[v-juanli-msft · Accepted solution]

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]

@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

[Zubair_Muhammad]

@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]

@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"