Results 1 to 4 of 4

Power Query Records access record filed in let statement,

Threaded View

  1. #1
    Forum Contributor
    Join Date
    08-03-2021
    Location
    London
    MS-Off Ver
    365
    Posts
    777

    Power Query Records access record filed in let statement,

    Hi,
    I have what I wanted as far as result but I think it may be better written,

    So within a let statement I wanted to get the record field values if TRUE so below produces true / false column

     Table.AddColumn(A, "Custom", each [ One = [Item] = "A" , 
                                                             Two = [Item] = "B", 
                                                             Three = [Item] = "C" , 
                                                                Four = [Item] = "D" ])
    This below gives me true and false that I want, but it means typing it all out each time I want to use it in a step

         Table.AddColumn(A, "Custom", each [ 
                                                                  One = if [Item] = "A" then 10 else "FALSE", 
                                                                    Two = if [Item] = "B" then 20 else "FALSE" , 
                                                                   Three = if [Item] = "C" then 30 else "FALSE" , 
                                                                         Four = if [Item] = "D" then 40 else "FALSE" ])
    Since original post I have got a result but if any one can suggest a neater / shorter way of wriing it plase let me know, I would like to use the 'one , two, three, four ' form the original records, but not sure this is possible. I will attach copy;

    This code i have is ;
     let R = 
                                      [ One = [Item] = "A" , 
                                           Two = [Item] = "B", 
                                         Three = [Item] = "C", 
                                              Four = [Item] = "D" ] , 
                                R2 = 
                                               [ One = if Record.Field( R, "One" )   then 10 else Record.Field(R, "One") ,
                                           Two = if Record.Field( R,"Two" )       then 20 else Record.Field( R, "Two") , 
                                             Three = if Record.Field( R, "Three")  then 30 else Record.Field( R, "Three") ,
                                               Four = if Record.Field( R,"Four" ) then 40 else Record.Field(R, "Four") ] 
                                       in R2


    Richard.
    Attached Files Attached Files
    Last edited by Dicken; 12-30-2023 at 09:45 AM. Reason: More information

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 2
    Last Post: 05-27-2023, 05:55 PM
  2. [SOLVED] Power query show records within dates
    By Undo in forum Excel General
    Replies: 6
    Last Post: 05-03-2022, 07:55 AM
  3. Display Duplicate Records using Power Query
    By alansidman in forum Tips and Tutorials
    Replies: 1
    Last Post: 07-14-2021, 12:48 PM
  4. Replies: 8
    Last Post: 10-21-2020, 03:02 AM
  5. [SOLVED] How to access data via VBA from a Connection Only Power Query query.
    By MLAN_75 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-14-2020, 11:45 AM
  6. Excel Power Query Refresh or Access Query - 2nd Query Run is faster
    By Steveapa in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-03-2020, 10:16 AM
  7. [SOLVED] Power Query: Combine records with deliminter
    By misty15 in forum Office 365
    Replies: 4
    Last Post: 09-17-2019, 04:34 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1