How to Transpose unique values and get matching values in rows?

0 votes

So, I'm attempting to transform this input table.

into this output table.

So, I've to do the following;

  • Get unique items in the first column
  • Transpose and promote these items as column headers
  • Get matching values under each unique header

 I need the output table to dynamically update each time. No VBA.

Apr 1 in Power BI by Shubham
• 12,230 points
14 views

1 answer to this question.

Your answer

Your name to display (optional):
Privacy: Your email address will only be used for sending these notifications.
0 votes

I think this query should do what you want:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ChangedType = Table.TransformColumnTypes(Source,{{"Type", type text}, {"Value", type text}}),
    GroupedRows = Table.Group(ChangedType, {"Type"}, {{"DistinctValues", each _[Value]}}),
    Output = Table.FromColumns(GroupedRows[DistinctValues], GroupedRows[Type])
in
    Output
answered Apr 1 by Upasana
• 7,600 points

Related Questions In Power BI

0 votes
1 answer

How to replace null values with custom values in Power BI(power query editor)?

Hi Nithin, To fill or replace the null or ...READ MORE

answered Mar 8 in Power BI by CHERUKURI
• 13,940 points
309 views
0 votes
1 answer

How to calculate cumulative Total and % in DAX?

Hi, If your table is ready with percentage ...READ MORE

answered Mar 18 in Power BI by CHERUKURI
• 13,940 points
134 views
0 votes
1 answer

How to categorize state, pin and city in power bi (Address)

After loading dataset, you can split in ...READ MORE

answered May 9 in Power BI by anonymous
• 13,940 points
7 views
0 votes
2 answers

How do I get token using javascript API while trying to embed graphs using Power BI

You need to use the generateEmbededToken function. ...READ MORE

answered Oct 3, 2018 in Power BI by Kalgi
• 35,800 points
148 views
0 votes
1 answer

How to export Power Queries from One Workbook to Another with VBA?

Try solving it using the Workbook. Query ...READ MORE

answered Oct 22, 2018 in Power BI by Annie97
• 2,190 points
131 views
0 votes
1 answer

Excel Power Query: Using List.MatchAny on a column value

try this. let TableA = ...READ MORE

answered Oct 22, 2018 in Power BI by Annie97
• 2,190 points
62 views
0 votes
1 answer

How can I search for multiple strings?

A simple solution is this: List.ContainsAny(Text.SplitAny("This is a test ...READ MORE

answered Oct 24, 2018 in Power BI by Upasana
• 160 points
72 views
0 votes
1 answer

Power Query Web request results in “CR must be followed by LF” Error

What I think is, it might look ...READ MORE

answered Oct 29, 2018 in Power BI by Shubham
• 12,230 points
61 views
0 votes
1 answer

To expand all lists in a row of lists at the same time without recurring values

Can't promise simple, but I have something ...READ MORE

answered Feb 19 in Power BI by Upasana
• 7,600 points
41 views
0 votes
1 answer

How can i see my plugin name in the Power BI Visual plugin List ?

there is a couple of errors in your ...READ MORE

answered Nov 8, 2018 in Power BI by Upasana
• 7,600 points
30 views

© 2018 Brain4ce Education Solutions Pvt. Ltd. All rights Reserved.
"PMP®","PMI®", "PMI-ACP®" and "PMBOK®" are registered marks of the Project Management Institute, Inc. MongoDB®, Mongo and the leaf logo are the registered trademarks of MongoDB, Inc.