Results 1 to 9 of 9

Thread: PQ - table update with conditional on cell

  1. #1

    PQ - table update with conditional on cell



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Dear all
    Below is on extraxt of a wider table with many lines and columns. I'id like to add columns showing only the account number (eg 1013000 etc) or account name (in CAPs) on the line where a date is inserted, and subsequently to delete all lines with no date.
    Can you please help on this one ?
    Thanks
    Best


    10130000 CAPITAL SOUSCRIT APPEELE VERSE
    01/01/17 AN 000 S.A.N.
    Total compte 10130000
    10410000 PRIME D'EMISSION
    01/01/17 AN 000 S.A.N.
    Total compte 10410000
    10610000 RESERVE LEGALE
    01/01/17 AN 000 S.A.N.
    Total compte 10610000
    Total classe 10
    11000000 REPORT A NOUVEAU
    01/01/17 AN 000 S.A.N.
    Total compte 11000000

  2. #2
    Conjurer Rudi's Avatar
    Join Date
    Jan 2014
    Location
    Cape Town, RSA
    Posts
    134
    Articles
    0
    Excel Version
    Excel 365 ProPlus
    Hi,

    Here is a quick and dirty way of doing it (on the data sample you posted)

    Code:
    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type date}, {"Column2", Int64.Type}, {"Column3", type text}}),
        #"Filled Up" = Table.FillUp(#"Changed Type",{"Column1"}),
        #"Replaced Errors" = Table.ReplaceErrorValues(#"Filled Up", {{"Column2", null}}),
        #"Filtered Rows" = Table.SelectRows(#"Replaced Errors", each ([Column2] <> null))
    in
        #"Filtered Rows"
    Regards,
    Rudi
    www.eileenslounge.com

  3. #3
    Hi Rudi,
    Thank you so much. It appears that there is an error message "Table1" not recognised. I tried changing the name with the actual name of the tabl on my Excel spreadsheet, but still does not work... May be an easy trick?

  4. #4
    Conjurer Rudi's Avatar
    Join Date
    Jan 2014
    Location
    Cape Town, RSA
    Posts
    134
    Articles
    0
    Excel Version
    Excel 365 ProPlus
    In order to create a solution I copied your sample data into Excel and started PQ with "From Table" --which created Table 1 in my reply.

    You probably will not have "Table 1" as a source so you need to replicate the steps I posted on your source.

    If you are familiar with the Advanced Code window, you can copy and paste the last 4 steps under your source step:

    Code:
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type date}, {"Column2", Int64.Type}, {"Column3", type text}}),
        #"Filled Up" = Table.FillUp(#"Changed Type",{"Column1"}),
        #"Replaced Errors" = Table.ReplaceErrorValues(#"Filled Up", {{"Column2", null}}),
        #"Filtered Rows" = Table.SelectRows(#"Replaced Errors", each ([Column2] <> null))
    If you are still stuck, I will upload a sample file...
    Let me know.
    Regards,
    Rudi
    www.eileenslounge.com

  5. #5
    I realise I must miss a very easy thing, but for some reasons, the best I could get is a table in a new column.
    Attached is a sample of data
    sorry for the hassle and thank you for your kind help
    Attached Files Attached Files

  6. #6
    Conjurer Rudi's Avatar
    Join Date
    Jan 2014
    Location
    Cape Town, RSA
    Posts
    134
    Articles
    0
    Excel Version
    Excel 365 ProPlus
    I followed the same steps on your table based on the sample query I posted earlier.
    I how the result is what you intended to get?

    testPQ.xlsx
    Regards,
    Rudi
    www.eileenslounge.com

  7. #7
    Not exactly. The tab Import contains the data to be imported and treated. The tab Result contains the desired results. basically, it means adding columns and insert the numbers and titles (CPAs) for each line that has a date on columnA. The numbers and titles change when appropriate. Hopefully the example on the spreadsheet is clearer than I am

  8. #8
    Conjurer Rudi's Avatar
    Join Date
    Jan 2014
    Location
    Cape Town, RSA
    Posts
    134
    Articles
    0
    Excel Version
    Excel 365 ProPlus
    Here is an updated version. Slowly getting there

    I'm not sure where the other column come from but you can guide me through that if you cannot work it from here.

    See attached.
    Attached Files Attached Files
    Regards,
    Rudi
    www.eileenslounge.com

  9. #9
    Looks awesome!
    Thank you so much. I still have some problems to save and reuse this request inserting the full database, but I will play round with this. The other columns are links with a table that I will construct in addition. I need to learn...
    Many thanks for you great and much appreciated help
    Best

Posting Permissions

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