Forum Discussion

garycarters's avatar
garycarters
Helper I
7 years ago
Solved

Splitting multi-element data structure list into separate rows

Hi all   I have a table that contains a number of control records.  Each control record can dictate the behaviour of an aspect of our system and may be a string, flag, file name or a list of data. ...
Need Help
  • Mariusz's avatar
    Mariusz
    7 years ago

    Hi garycarters 

     

    1. Create new Source >> Blank Query 

    2. Go to Advanced Editor and paste the Function Code Provided.

    3. In your table, go to Add Column ribbon and find Invoke Custom Function 

    4. In the Function Query section select the function you have created (most probably Query1 if you have not change the name ).

    5. In Your Column Section change ABC to Column Name and Select ListData from the drop down list next press OK to confirm.

    6. Now you should have an extra column in your table that can be expanded by:

    • Clicking inside the cell on "Table" 
    • If you want to expand on the original table, click on the double arrow on the column header and select the columns you want to add
    Best Regards,
    Mariusz

    Please feel free to connect with me.
    Mariusz Repczynski

      

     

garycarters's avatar
garycarters
Helper I
7 years ago

Sorry been away for a couple of days.

 

My Table query is now this;

let
    Source = Sql.Database("calsql", "mcsrm_live"),
    dbo_spec_checklist_items = Source{[Schema="dbo",Item="spec_checklist_items"]}[Data],
    #"Filtered Rows" = Table.SelectRows(dbo_spec_checklist_items, each ([id] = 57)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"charge_item", "doc_path", "used_for_equipment", "used_for_contracts", "used_for_works_orders", "view_from_web", "used_for_customers", "used_for_suppliers", "used_for_employees", "SpecificationAutoCreate", "SpecificationMandatory", "UsedForPurchaseOrders", "UsedForPATTests", "UsedForQuotes", "UsedForPODistributions", "UsedForContacts", "UsedForProspects", "UsedForInvoices", "Redact", "UsedForJobs", "UsedForCases"}),
    #"Extracted ListData" = Table.TransformColumns(#"Removed Columns", {{"ListData", each Text.Middle(_, 5, 200000), type text}})
in
    #"Extracted ListData"

Column ListData now contains the list of values I wish to break into Rows of 2 columns (ID and description).

 

I've created a custom column with the code you supplied

 

How do I invoke the function you have provided from here to give me the rows / columns I originally outlined?  This is all new (but very exciting) ground for me.

Mariusz's avatar
Mariusz
Community Champion
7 years ago

Hi garycarters 

 

1. Create new Source >> Blank Query 

2. Go to Advanced Editor and paste the Function Code Provided.

3. In your table, go to Add Column ribbon and find Invoke Custom Function 

4. In the Function Query section select the function you have created (most probably Query1 if you have not change the name ).

5. In Your Column Section change ABC to Column Name and Select ListData from the drop down list next press OK to confirm.

6. Now you should have an extra column in your table that can be expanded by:

  • Clicking inside the cell on "Table" 
  • If you want to expand on the original table, click on the double arrow on the column header and select the columns you want to add
Best Regards,
Mariusz

Please feel free to connect with me.
Mariusz Repczynski