• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Retrieve value for entire column

K

Kelly A

Member
I have Table1 that only has 1 cell of data in it. I have Table2 that would like to retrieve the value of Table1 and populate it down an entire column. Need your assistance please.
 

Attachments

  • tCustomColumn.xlsx
    17.7 KB · Views: 2
Cannot review your workbook as your source data is embedded in a workbook that is resident in your computer and not part of the spreadsheet.
 
Code: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"PO", Int64.Type}, {"Number", type any}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Table1[Number]),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From)), type text})
in
    #"Extracted Values"
 

Attachments

  • tCustomColumn (1).xlsx
    20.6 KB · Views: 2
Thank you, but I received an error message when adding this formula to the sample file I attached. Error is attached
 

Attachments

  • tCustomColumn Error.docx
    158.6 KB · Views: 5
Sorry, Suggest you google this as it is not an error with the Mcode but something is amiss in your file and how you are sourcing the data.
 
It is the same file I posted here. It is 1 table with 1 cell/column/row and another table with 2 columns and 2 rows. I did Get Data From Other Sources, from table. I don't understand your comment about how I am sourcing the data.
 
In the file I posted in #4, the result I show is
PONumberCustom
12345​
7891234
45678​
7891234
I don't know what is happening on your end, but isn't the above your desired results. I get no error. You originally posted the source data was from another file and not embedded in the current file as shown in my solution. Is your Mcode exactly the same as what I posted or did you modify the source line?
 
Sorry Kelly, but I am not understanding your issue. In the sample your provided, I have given you in the file I have provided what you asked for. Not understanding what issues you are having. Does your actual file not resemble the sample you provided? If you are not getting expected results then show us the expected results within your excel file. An error message in a word document is not helpful as nothing can be manipulated nor analyzed from that.
 
I only tested your code in the sample file. Yes, the actual file contains different data but I haven't used your code there. The sample file can contain any data at all, abc, 123, just trying to retrieve the value in the other table without a matching column. OR another suggestion if that is easier.
 
Last edited:
Without seeing a true representative sample, I can not offer any further help. We are not mind readers here. I can only help (sometimes) only if I understand the whole issue.
 
Kelly
My post #4 reacted and responded to that post in #5. I don't know what else you are looking for as that is the solution I have been referring to subsequently. If that is not what you are looking for, then please show us a mocked up solution.
 
Here is the updated file with your code for your convenience. Error message is received when adding the custom column with the code. Please advise.
 

Attachments

  • tCustomColumn.xlsx
    22 KB · Views: 1
Last edited:
I have modified your Mcode to achieve your expected results. Your PO Type was originally a number. It is now text. Formatting consistently was an issue as well as not using the entire code. This works for this example.

Code: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"PO", type text}, {"Number", type any}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Table1[Number]),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From)), type text}),
    #"Removed Columns" = Table.RemoveColumns(#"Extracted Values",{"Number"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Number"}})
in
    #"Renamed Columns"
 

Attachments

  • tCustomColumn (3).xlsx
    22.8 KB · Views: 0
I tried the code and got the following error.
Expression.Error: A cyclic reference was encountered during evaluation.
 
Again, my code works with your example. If you are changing things up then you need to provide an example of your actual data and the code you're using with it. I cannot fix what I cannot see.
 
I did not change anything, I used your code in the sample file only. As soon as I added the code, I received the error.
 
When I open your file here is the Mcode you have which is not the same as what I have given you.

Code: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"PO", type text}, {"Number", type any}}),
    #"Added Custom" = let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"PO", Int64.Type}, {"Number", type any}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Table1[Number]),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From)), type text})
in
    #"Extracted Values"
in
    #"Added Custom"

In Power Query, on the Home Tab, click on Advanced Editor and look at what I have and replicate it.
 
Alan, thank you, I was pasting it into the custom column formula and not the advanced editor so I was getting the circular reference error. I am new to power query and learning it on the fly for my job so please pardon me. I am going to try this in my real file and see if I can get it to work. I will publish my outcome here. Thank you for hanging in there and being patient with me. Bless you!
 
Kelly, if you really want to Excel with PQ, then pick up this book by Ken Puls. Easy to follow and you will be humming along quickly

Amazon.com: Master Your Data with Power Query in Excel and Power BI: Leveraging Power Query to Get & Transform Your Task Flow: 9781615470587: Escobar, Miguel, Puls, Ken: Books

Amazon.com: Master Your Data with Power Query in Excel and Power BI: Leveraging Power Query to Get & Transform Your Task Flow: 9781615470587: Escobar, Miguel, Puls, Ken: Books
www.amazon.com
 
So, it is not finding the column name from the #"Added Custom". Can I post in the private area as my data is sensitive.
 
You must log in or register to reply here.
Back
Top