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
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
Solved! Go to Solution.
Hi @holydragon ,
I believe that this link has the answer you are looking for:
Proud to be a Datanaut!
Private message me for consulting or training needs.
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
Hi @holydragon ,
I believe that this link has the answer you are looking for:
Proud to be a Datanaut!
Private message me for consulting or training needs.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
107 | |
106 | |
91 | |
67 |
User | Count |
---|---|
162 | |
133 | |
132 | |
93 | |
91 |