Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
holydragon
Frequent Visitor

Need help parsing a long list of record in Excel file

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

1 ACCEPTED SOLUTION
collinq
Super User
Super User

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




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

Proud to be a Datanaut!
Private message me for consulting or training needs.




View solution in original post

2 REPLIES 2
v-luwang-msft
Community Support
Community Support

Hi @holydragon ,

Pls refer the below:

Base data:

vluwangmsft_0-1655891656775.png

Output result:

vluwangmsft_1-1655891668910.png

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
Super User
Super User

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




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

Proud to be a Datanaut!
Private message me for consulting or training needs.




Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.