Need help parsing a long list of record in Excel file

[holydragon]

Hello,

I have a list of records in an Excel file.   The parameter type is in column A and it's value is in column B. 

Summary
User :	John Doe
User Name :	jDoe
Job Location	Heaven's Circle

Summary
User :	John Doe
User Name :	jDoe
Job Location :	Heaven's Circle

There are hundreds of records and I would like to transpose and place the records into a single table

User           User Name    Job Location

John Doe   jDoe              Heaven's circle

etc..

Would you provide some guidance on how I should address this? 

Best,

holydragon

[v-luwang-msft]

Hi @holydragon ,

Pls refer the below:

Base data:

Output result:

Fields	Data
User	A
User Name	A1
Job Location	A2
User	B
User Name	B1
Job Location	B2
User	C
User Name	C1
Job Location	C2

Refer the below:

let
    Source = Csv.Document(File.Contents("\\ip\File15\Hua Yu\Xue Ding\username\Download\data.csv"),[Delimiter=",", Columns=2, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Fields", type text}, {"Data", type text}}),
    #"Raggruppate righe" = Table.Group(#"Changed Type", {"Fields"}, {{"all", each fn_rectab(_)}},GroupKind.Local,(x,y)=>Number.From(y[Fields]="User")),
    #"Rimosse altre colonne" = Table.SelectColumns(#"Raggruppate righe",{"all"}),
    #"Expanded all" = Table.ExpandTableColumn(#"Rimosse altre colonne", "all", {"User", "User Name", "Job Location"}, {"all.User", "all.User Name", "all.Job Location"})
in
    #"Expanded all"

let

    trec=(grp)=>

    let
    trc=Table.RenameColumns(grp,{ {"Fields","Name"},{"Data","Value"}}),
    #"Raggruppate righe1" = Table.Group(trc, {"Name"}, {{"rec", each Record.FromTable(_)}},GroupKind.Local,(x,y)=>Number.From(y[Name]="A")),
    #"Tabella rec espansa" = Table.ExpandRecordColumn(#"Raggruppate righe1", "rec", {"User", "User Name", "Job Location"},  {"User", "User Name", "Job Location"}),
    #"Ricopiato in basso" = Table.FillDown(#"Tabella rec espansa",{"User","User Name", "Job Location"}),
   
    #"Rimosse colonne" = Table.RemoveColumns(#"Ricopiato in basso",{"Name"})
in
    #"Ricopiato in basso"


in trec

Did I answer your question? Mark my post as a solution!

Best Regards

Lucien

[collinq · Accepted solution]

Hi @holydragon ,

I believe that this link has the answer you are looking for:

Solved: Re: Splitting a repeating set of columns in a row ... - Power Platform Community (microsoft....