cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Shimi43
Frequent Visitor

Using Record.Field to pass column name variable

I'm trying to create a loop in order to create columns from sections in text and seperate the sections out from a big paragraphs.

 

I first did some test ones that worked perfectly (see the following code)

 

= Table.AddColumn(#"20.b. Determine Following Section", "LATEX", each Text.BetweenDelimiters([Data],"LATEX",[Next Section after LATEX]))

 

This outputs exactly what I want.  Change "LATEX" to any of the other items "CAP", "STALK", etc. and they all work how I want them to.

 

So I decided to put it in a loop.  But apparently I can't just use a variable and pass it through in brackets like so: [Variable]

 

I read online a lot of people recommend using Record.Field.  But when I use it I constantly get the program reading it as a Function and not text (see image on error).  So I'm not sure what to do.

 

Here is how I run the function:

= fxSectionInfoGrab(#"20.b. Determine Following Section", Section_Headers)

 

Here is the function:

 

let
fxSectionInfoGrab = (DataTable as table, PotentialSectionEnds as table) as any =>
let
// Turn the table into a list
Section_List = Table.ToList(PotentialSectionEnds),

// Determine how many ends are in the list
TotalLoops = List.Count(Section_List),

fxSectionInfo = (DataTableTemp, CurrentLoop) =>
let
// Determine Section Working on
Section_Name = Section_List{CurrentLoop},

// Determine Next Section Column to grab from
Next_Section_Column = "Next Section after " & Section_List{CurrentLoop},

// Determines Next Section Name
//Next_Section = each Record.Field(DataTableTemp, Next_Section_Column),

// Creates TempTable to store
tempTable = Table.AddColumn(DataTableTemp, Section_Name, each Text.BetweenDelimiters([Data], Section_Name, each Record.Field(DataTableTemp, Next_Section_Column)), Text.Type)
in
// Determin the Next Section Name
if CurrentLoop >= TotalLoops-1
then tempTable
else @fxSectionInfo(tempTable, CurrentLoop+1),

//Evaluate the sub-function at the first row
Output = fxSectionInfo(DataTable, 0)
in
Output
in
fxSectionInfoGrab

 

 

 

Here is the  Section_Headers Table

 

Section_Headers
CAP
LATEX
GILLS
STALK
VEIL
HABITAT
EDIBILITY
SPORE_PRINT
COMMENTS
N/A

 

Shimi43_0-1660200994656.png

Due to Character requirements I've included a sample of the code in a reply.

Thank you in advance.

 

 

1 ACCEPTED SOLUTION

Figured it out on my own.  Here is the code for the function in case anyone was curious.

 

let
fxSectionInfoGrab = (DataTable as table, PotentialSectionEnds as table) as any =>
let
// Turn the table into a list
Section_List = Table.ToList(PotentialSectionEnds),

// Determine how many ends are in the list
TotalLoops = List.Count(Section_List),

fxSectionInfo = (DataTableTemp, CurrentLoop) =>
let
// Determine Section Working on
Section_Name = Section_List{CurrentLoop},

// Determine Next Section Column to grab from
Next_Section_Column = "Next Section after " & Section_List{CurrentLoop},

// Creates TempTable to store
tempTable = Table.AddColumn(DataTableTemp, Section_Name, each Text.BetweenDelimiters([Data], Section_Name, Record.Field(_, Next_Section_Column)), Text.Type)

in
// Determin the Next Section Name
if CurrentLoop >= TotalLoops-1
then tempTable
else @fxSectionInfo(tempTable, CurrentLoop+1),

//Evaluate the sub-function at the first row
Output = fxSectionInfo(DataTable, 0)
in
Output
in
fxSectionInfoGrab

View solution in original post

2 REPLIES 2
Shimi43
Frequent Visitor

A sample of the data I'm working with:

 

GenusSpeciesCommon NameGroupDataNext Section after CAPNext Section after LATEXNext Section after GILLSNext Section after STALKNext Section after VEILNext Section after HABITATNext Section after EDIBILITYNext Section after SPORE_PRINTNext Section after COMMENTSNext Section after N/A
               
LactariusdeliciosusDelicious Milk CapNoCAP 5-16 cm broad, broadly convex or with a depressed center and an inrolled margin when young, becoming depressed or shallowly funnel-shaped; surface viscid when moist but soon dry, smooth, often zoned; color variable: dull orange to carrot-orange or orange- brown, sometimes blotched with or entirely green; fading in age or dry weather to brownish, gray, dull greenish-gray, or even yellowish; margin inrolled when young. Flesh thick, brittle, orange to yellowish or greenish; taste mild or slightly bitter. LATEX very scanty, bright carrot-orange (but in some forms slowly staining dark red when exposed), eventually staining wounded or aged tissue greenish (within 5 hours). GILLS typically bright to dull orange, but varying to yellowish or orange-buff, greenish where wounded; adnate to decurrent, close. STALK 2-7 cm long, 1-2.5 cm thick, equal or narrowed at base, soon dry, sometimes scrobiculate; rigid, hollow inage, frequently maggot-riddled, colored like cap or paler. SPORE_PRINT creamy yellowish-buff; spores 7-11 x 6-8 microns, broadly elliptical to nearly round, with amyloid ridges. HABITAT Scattered to gregarious or in troops under conifers (pine, spruce, etc.), common and widely distributed. It is abundant in our coastal pine forests in the late fall and winter, generally after the major crop of look-alike L. rubrilacteus. EDIBILITY Edible, but not necessarily delicious. Several varieties or forms occur and some are apparently better than others. Special treatment is required to overcome the grainy texture and latent bitterness. Its abundance and distinctiveness, however, make experimentation worthwhile. Some sources recommend slow cooking (e.g., baked in a casserole), others insist it should be cooked rapidly in a frying pan with very little butter. It is popular in Europe, and the Russians are fanatical about it-especially salted. COMMENTS This variable, cosmopolitan fungus strikes a discordant but colorful note with its unlikely combination of pistachio-green and carrot-orange-even more so when found in the company of the bright red Russula rosacea, as is so often the case in our area. The carrot-colored latex separates it from L. rubrilacteus, and confusion with other mushrooms is unlikely. The latex is so scanty as to often be non-existent, but the brittle flesh and greenish stains on the cap, stalk, and! or gills will identify it. The fruiting bodies persist for a long time and are often completely hollowed out by maggots. Several varieties of L. deliciosus have been described based on slight differences in color, staining reactions, and microscopic characteristics; one variety tends to have an areolate (scaly-cracked) cap in age. The Greeks were apparently fond of L. deliciosus, for it is depicted on a fresco from Herculaneum (buried in 79 A.D.). Other species: L. thyinos, of northern bogs, has carrot- orange latex but stains red, not green; in other respects it is quite similar. L. salmoneus is a smallish but distinctive southeastern species with a whitish cap, bright orange gills, flesh, and stalk (when fresh) and orange to salmon-orange latex. L. pseudodeliciosus is buff or dingy colored with yellow-orange to rusty-orange latex. All ofthese are edible and the latter two stain greenish in age or where wounded.LATEXGILLSSTALKSPORE_PRINTN/AEDIBILITYCOMMENTSHABITATN/AN/A
LactariusrubrilacteusBleeding Milk CapNoCAP 4-14 cm broad, broadly convex with a depressed center and inrolled margin when young, depressed or shallowly funnel-shaped in age; surface viscid when moist, smooth, reddish-brown to orange, orange-brown, or tan, or often concentrically zoned with these colors; duller and greenish-stained in age. Flesh thick, brittle, brownish to buff, reddish, etc.; taste mild or slightly bitter. LATEX scanty, dark red (but occasionally orange-red in old specimens), slowly staining wounded areas greenish. GILLS adnate to slightly decurrent, close, reddish or dull purplish-red, or tan with a dark reddish sheen; greenish where wounded. STALK 2-6 cm long, 1-2.5 cm thick, equal or narrowed below, firm, rigid, hollow, colored like cap or paler, sometimes scrobiculate. SPORE_PRINT pale yellowish or buff; spores 7.5-10 )( 6-8 microns, broadly elliptical to nearly round, with amyloid ridges. HABITAT Scattered or in large troops under conifers throughout the West; associated in our area with Douglas-fir and abundant in the fall and early winter. Where pines pre- dominate it is largely supplanted by L. deliciosus, and where pine and Douglas-fir grow together, the two milk caps often mingle. In Europe, L. sanguifluus (which may be the same as L. rubrilacteus) is mycorrhizal with pine. EDIBILITY Edible, but not pleasing to everyone because of its granular texture; usually better, however, than L. deliciosus (see comments on edibility of that species). COMMENTS The dark red latex is the telltale trait of this handsome fungus, which for many years has been known to fungophiles as L. sanguifluus. In dry or very wet weather the latex may be absent, but in these conditions the greenish stains are usually quite pronounced. In fact, as with L. deliciosus, weathered fruiting bodies may be entirely green, and mature specimens often have tiny, aborted green "buttons" at their bases. Be sure not to confuse this species with the similarly colored L. vinaceorufescens and L. chrysorheus-they often grow with L. rubrilacteus, but have a white latex that quickly turns yellow. Other species: L. barrows;; has dark red latex, but its cap is much paler (whitish to pinkish-brown) and it occurs mainly with ponderosa pine. Another similar edible species, L. subpurpureus of eastern North America, has wine-red latex and a wine- red to silvery cap, and favors hemlock. See also L. paradoxus(under L. indigo).LATEXGILLSSTALKSPORE_PRINTN/AEDIBILITYCOMMENTSHABITATN/AN/A
LactariusindigoIndigo Milk CapNoCAP 4-15 cm broad, convex or centrally depressed with an inrolled margin when young, usually depressed in age; surface smooth, viscid when moist, then dry; indigo-blue when fresh but fading to grayish- or silvery-blue, sometimes with greenish stains; often zoned concentrically. Flesh pallid to bluish, brittle, slowly staining greenish; taste mild to slightly bitter-acrid. LATEX indigo-blue (bright dark blue), scanty, slowly staining wounded tissue greenish. GILLS adnate to slightly decurrent, close, indigo-blue becoming paler in age. STALK 2-6 cm long, 1-2.5 cm thick, indigo-blue to silvery- or grayish-blue, equal or narrowed at base, rigid, hollow in age, soon dry. SPORE_PRINT creamy-yellowish; spores 7 -9 )( 5.5-7.5 microns, elliptical to nearly round, with amyloid warts and ridges. HABITAT Scattered to gregarious in summer and fall, mostly in oak and pine woods; found throughout soutt-.ern and eastern North America, but most common along the Gulf Coast and in Mexico. I have found it in Arizona under ponderosa pine. If it should turn up in southern California it would certainly be a most welcome addition to our fungus flora. EDIBILITY Edible and very good-superior, at least, to the other greenish-staining milk caps, such as L. deliciosus. In Mexico it is sometimes sold in farmer's markets. COMMENTS The overall blue to blue-gray color and bright blue latex make this one of the safest and most memorable of all agarics. No other milk cap has blue latex, let alone a bluish fruiting body. Other species: L. paradoxus, common under pine in eastern North America (especially the South), often has a bluish-tinged cap when young, but has reddish- brown to purple-brown latex and gills. Another species, L. chel;don;um, has a yellowish to dingy yellow-brown to bluish-gray cap and yellowish to brown latex "the color of grasshopper juice." It occurs commonly in the eastern United States and Southwest. L. hem;cyaneus is a medium-sized to large species which usually has bluish flesh in the cap and orange to red-orange flesh in the base of the stalk. It may possibly occur in southern California, according to Greg Wright and Paul Harding. All of the above species are edible despite their tendency to stain green in age.LATEXGILLSSTALKSPORE_PRINTN/AEDIBILITYCOMMENTSHABITATN/AN/A
CamarophyllusborealisSnowy Waxy CapNoCAP 1-5 cm broad, convex or obtusely umbonate, often expanding in age to plane or depressed; surface smooth, moist or lubricous but not viscid, watery white to dull white. Flesh thin, soft, white, odor mild. GILLS usually decurrent, well-spaced, thick, soft, somewhat waxy; white. STALK 2-9 cm long, 2-5 (8) mm thick, equal or tapering down- ward, smooth, firm, dry, often curved or sinuous, white. VEIL absent. SPORE_PRINT white; spores 7-9 (12»( 4.5-6.5 microns, elliptical, smooth. Gill tissue interwoven. HABITAT Scattered or in groups on ground and humus in woods or at their edges, widely distributed. In our area this species and its relatives C. virgineus and C. niveus (see com- ments) are fairly common throughout the mushroom season, but seldom occur in the large numbers typical of Hygrophorus eburneus. The best fruitings usually occur in the winter, often in relatively dry weather. EDIBILITY Edible but fleshless, flavorless, and savorless. COMMENTS Also known as Hygrophorus borealis, this is one of several rather small, whitish waxy caps with a dry to slightly viscid (not slimy) cap. slender non-viscid stalk, and interwoven gill tissue. Others include: C. niveus, common. cap slightly viscid and slightly striate when moist; C. virgineus, especially common in California. cap dry and usually tinged yellow in age or dry weather. and spores 8-12 microns long; C. angustiJolius, dull white with spores 5-8 microns long; C. cremicolor, with pale yellow gills when young; and three species with parallel gill hyphae and non-decurrent gills: Hygrocybe subaustrali- ga, small, cap scarcely viscid, gills notched or adnexed, fairly common; H. albinella. cap white and conical at first, stalk dry. rare; and H.fornicata. with a grayish-tinged viscid cap. All ofthese have been placed in Hygrophorus. None have the slimycapand stalk of Hygro- phorus eburneus. Alboleptonia sericella and Inocybe geophylla can be similar but have pinkish and brown spores respectively. See also the undescribed Marasmius on p. 206.GILLSN/ASTALKVEILSPORE_PRINTEDIBILITYCOMMENTSHABITATN/AN/A
CamarophyllusrussocoriaceusCedar Waxy CapNoCAP 1-3 (5) cm broad. convex to plane or slightly umbonate to slightly depressed; surface smooth, not viscid. white or often tinged pale tan to yellowish. especially at the center. Flesh whitish, thin; odor fragrant when fresh (like arborvitae or cedar). GILLS well-spaced, usually decurrent. thick, slightly waxy, white or whitish. STALK 3-10 cm long, 2-5 (8) mm thick, equal or tapered downward. often long in relation to cap; dry, smooth, colored more or less like cap, often curved or sinuous. VEIL absent. SPORE_PRINT white; spores 6.5-9)( 4-6 microns, elliptical, smooth. Gill tissue interwoven. HABITAT Widely scattered or in small groups in woods or at their edges, known only from the west coast (in addition to Europe). Common in our area in the fall and winter, especially at higher elevations in the coastal mountains, but rarely in large numbers. EDIBILITY Not recommended-it has a slightly medicinal flavor, at least raw. COMMENTS The piquant cedarlike odor distinguishes this plain-looking waxy cap from C. borealis and other look-alikes. Other species: Hygrophoruspusillusisasomewhat similar species with a viscid cap that is usually tinged cream or pale brownish-flesh-color. I t has a slight fragrant odor, divergent to nearly parallel gill tissue, and occurs in groups or troops under conifers in northern California and the Pacific Northwest.          

Figured it out on my own.  Here is the code for the function in case anyone was curious.

 

let
fxSectionInfoGrab = (DataTable as table, PotentialSectionEnds as table) as any =>
let
// Turn the table into a list
Section_List = Table.ToList(PotentialSectionEnds),

// Determine how many ends are in the list
TotalLoops = List.Count(Section_List),

fxSectionInfo = (DataTableTemp, CurrentLoop) =>
let
// Determine Section Working on
Section_Name = Section_List{CurrentLoop},

// Determine Next Section Column to grab from
Next_Section_Column = "Next Section after " & Section_List{CurrentLoop},

// Creates TempTable to store
tempTable = Table.AddColumn(DataTableTemp, Section_Name, each Text.BetweenDelimiters([Data], Section_Name, Record.Field(_, Next_Section_Column)), Text.Type)

in
// Determin the Next Section Name
if CurrentLoop >= TotalLoops-1
then tempTable
else @fxSectionInfo(tempTable, CurrentLoop+1),

//Evaluate the sub-function at the first row
Output = fxSectionInfo(DataTable, 0)
in
Output
in
fxSectionInfoGrab

Helpful resources

Announcements
Difinity Conference

Difinity Conference 2022

Difinity is the largest Microsoft Data, AI, Power BI, Power Platform and Business Applications Conference in New Zealand.

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.