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
• 13,290 points
55 views

1 answer to this question.

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
• 8,530 points

Related Questions In Power BI

+1 vote
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
• 25,900 points
3,804 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
• 25,900 points
1,127 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
• 25,900 points
33 views
0 votes
3 answers

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

Use ADAL.js that will give you an ...READ MORE

answered Jun 22 in Power BI by anonymous
572 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
356 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
196 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
149 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
• 13,290 points
148 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
• 8,530 points
178 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
• 8,530 points
49 views