Power Query question

scott_86_

New Member
Joined
Sep 27, 2018
Messages
46
Office Version
  1. 365
Platform
  1. Windows
Hi,

Can anyone help me out with some applied steps for the below? I've tried a mixture of transpose, promote first row to header, unpivot, etc; however, so far am not having any luck!

(Apologizes, I am unable to upload a mini-sheet due to my organisations restrictions).

In the sample data below, I am trying to achieve the following:

1727247521182.png
 
Should I be pasting your code straight it the formula bar? I'm still not having any luck. Probably something really simple I'm missing!!

The steps I am taking is:
  1. Table1 data to PQ
  2. Pasting in provided sample code
  3. Still receiving the following error:
Expression.Error: A cyclic reference was encountered during evaluation.

The advanced editor shows the following:

Power Query:
let

    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type any}}),
    Custom1 = #table(List.FirstN(Table1[Column1], 3), List.Split(Table1[Column2], 3))

in

    Custom1

Screenshot of the error once code is pasted in:

1727441023162.png
 
Upvote 0

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
The last formula should be:

Power Query:
= #table(List.FirstN(#"Changed Type"[Column1], 3), List.Split(#"Changed Type"[Column2], 3))
 
Upvote 0

Forum statistics

Threads
1,226,504
Messages
6,191,426
Members
453,657
Latest member
DukeJester

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top