Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
appeljr
Frequent Visitor

Combine two or more rows between two words (datasource with random carriage returns)

I'm pulling book data from OCLC WorldCat which is uneven, at best, when it comes to the book Title. What I need is to identify the portion of the Title that is getting knocked into Column1.1 prior to Author and concat any found title chunks after the first Title line. I thought maybe I could use non-colon delimiters, but not all the title lines have punctuation. In this table I've split by the first colon. I've enclosed the same chunk of source text, below.

 

Column1.1Column1.2
AvailabilityCheck the catalogs in your library. Libraries worldwide that own item: 7 Connect to the Penn Libraries catalog
External ResourcesCite This Item
Find Items AboutMayer, Christian,max: 29
TitleA letter from M. Christian Mayer, Astronomer to the Elector Palatine, to Mr. N.N. on the going of a new pendulum clock, made by Mr. John Arnold, and set up in the Elector's
observatory at Manheim. (Translated from the German.). 
Author(s)Mayer, Christian, 1719-1783.
PublicationLondon : Printed for T. Becket,
Year1781
Descriptionviii, 19, [1] pages ; 4to
LanguageEnglish
Note(s)Reproduction:           Microfilm./ Woodbridge, CT/ Research Publications, Inc.,/ 1986./ 1 reel ; 35mm./ (The Eighteenth Century ; reel 6472, no. 05).
Material TypeMicrofiche (mfc); Microfilm (mfl)
Document TypeBook
Entry19850205
Update20160924
Accession NoOCLC:                   224659971
ProviderOCLC
DatabaseWorldCat
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
AvailabilityCheck the catalogs in your library. Libraries worldwide that own item: 7 Connect to the Penn Libraries catalog
External ResourcesCite This Item
Find Items AboutMayer, Christian,max: 29
TitleBasis Palatina ...
exeunte anno superiore 1762 ad norman Academiae regiae parisinae scientiarum exactam bis dimensa hoo demum anno 1763 novis mensuris aucta et confirmata recentissimisque observationibus et calculis 
stabilita, 
Author(s)Mayer, Christian, 1719-1783.
PublicationMannhemii, ex Typographejo electorali-aulico
Year1763
Description12 preliminary leaves, 23, [2] pages 23 cm
LanguageLatin
SUBJECT(S) 
DescriptorBase measuring.
Triangulation. 
Base measuring. 
Triangulation. 
Note(s)Half-title with vignette (12th preliminary leaf): Series et ordo triangulorum quae ex propriis suis observationibus anno 1763 habitis deduxit et correxit./ Head and tail
pieces. 
Class DescriptorsLC: QB303
Responsibilitya Christiano Mayer ...
Document TypeBook
Entry19891006
Update20150314
Accession NoOCLC:                   23618319
ProviderOCLC
DatabaseWorldCat
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
AvailabilityCheck the catalogs in your library. Libraries worldwide that own item: 7 Connect to the Penn Libraries catalog
External ResourcesCite This Item
Find Items AboutMayer, Christian,max: 29
TitleSolis et lunae eclipseos observatio astronomica ...
facta Schwezingae in specula nova electorali anno 1764 diebus 17 martii et 1 aprilis comparata pluribus Europae celebrioribus observationibus ejusdemque clementissimi principis honoribus oblata 
quarto idus julii ejusdem anni / 
Author(s)Mayer, Christian, 1719-1783.
PublicationMannheimii : Ex typographejo electoraliaulico,
Year1764
Description[18], 70 pages : tables ; 23 cm
LanguageLatin
SUBJECT(S) 
DescriptorLunar eclipses -- 1764.
Solar eclipses -- 1764. 
Astronomy -- Early works to 1800. 
Astronomy. 
Lunar eclipses. 
Solar eclipses. 
Genre/FormEarly works.
Time1764
Responsibilitya Christiano Mayer ...
Document TypeBook
Entry19831121
Update20161208
Accession NoOCLC:                   10645217
ProviderOCLC
DatabaseWorldCat
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
  
  
  

 

 

Availability:           Check the catalogs in your library. Libraries worldwide that own item: 7 Connect to the Penn Libraries catalog
External Resources:     Cite This Item
Find Items About:       Mayer, Christian,max: 29
Title:                  A letter from M. Christian Mayer, Astronomer to the Elector Palatine, to Mr. N.N. on the going of a new pendulum clock, made by Mr. John Arnold, and set up in the Elector's
observatory at Manheim. (Translated from the German.).
Author(s):              Mayer, Christian, 1719-1783.
Publication:            London : Printed for T. Becket,
Year:                   1781
Description:            viii, 19, [1] pages ; 4to
Language:               English
Note(s):                Reproduction:           Microfilm./ Woodbridge, CT/ Research Publications, Inc.,/ 1986./ 1 reel ; 35mm./ (The Eighteenth Century ; reel 6472, no. 05).
Material Type:          Microfiche (mfc); Microfilm (mfl)
Document Type:          Book
Entry:                  19850205
Update:                 20160924
Accession No:           OCLC:                   224659971
Provider:        OCLC
Database:               WorldCat
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Availability:           Check the catalogs in your library. Libraries worldwide that own item: 7 Connect to the Penn Libraries catalog
External Resources:     Cite This Item
Find Items About:       Mayer, Christian,max: 29
Title:                  Basis Palatina ...
exeunte anno superiore 1762 ad norman Academiae regiae parisinae scientiarum exactam bis dimensa hoo demum anno 1763 novis mensuris aucta et confirmata recentissimisque observationibus et calculis
stabilita,
Author(s):              Mayer, Christian, 1719-1783.
Publication:            Mannhemii, ex Typographejo electorali-aulico
Year:                   1763
Description:            12 preliminary leaves, 23, [2] pages 23 cm
Language:               Latin
SUBJECT(S)
Descriptor:             Base measuring.
Triangulation.
Base measuring.
Triangulation.
Note(s):                Half-title with vignette (12th preliminary leaf): Series et ordo triangulorum quae ex propriis suis observationibus anno 1763 habitis deduxit et correxit./ Head and tail
pieces.
Class Descriptors:       LC: QB303
Responsibility:         a Christiano Mayer ...
Document Type:          Book
Entry:                  19891006
Update:                 20150314
Accession No:           OCLC:                   23618319
Provider:        OCLC
Database:               WorldCat
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Availability:           Check the catalogs in your library. Libraries worldwide that own item: 7 Connect to the Penn Libraries catalog
External Resources:     Cite This Item
Find Items About:       Mayer, Christian,max: 29
Title:                  Solis et lunae eclipseos observatio astronomica ...
facta Schwezingae in specula nova electorali anno 1764 diebus 17 martii et 1 aprilis comparata pluribus Europae celebrioribus observationibus ejusdemque clementissimi principis honoribus oblata
quarto idus julii ejusdem anni /
Author(s):              Mayer, Christian, 1719-1783.
Publication:            Mannheimii : Ex typographejo electoraliaulico,
Year:                   1764
Description:            [18], 70 pages : tables ; 23 cm
Language:               Latin
SUBJECT(S)
Descriptor:             Lunar eclipses -- 1764.
Solar eclipses -- 1764.
Astronomy -- Early works to 1800.
Astronomy.
Lunar eclipses.
Solar eclipses.
Genre/Form:             Early works.
Time:                   1764
Responsibility:         a Christiano Mayer ...
Document Type:          Book
Entry:                  19831121
Update:                 20161208
Accession No:           OCLC:                   10645217
Provider:        OCLC
Database:               WorldCat
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Any help is most apprciated!

 

1 ACCEPTED SOLUTION

Hi @appeljr ,

please find a possible solution in the file attached.

 

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

4 REPLIES 4
Greg_Deckler
Super User
Super User

So, is what you are importing essentially a text file formatted the way you have posted? @ImkeF  might be able to assist.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Yes, @Greg_Deckler and @ImkeF : it's a plaintext file as formatted in the code box, and there's sadly no cleaner (fast) source for the kind of bulk pull I'm doing.

Hi @appeljr ,

please find a possible solution in the file attached.

 

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

appeljr
Frequent Visitor

Perfect! I've learned so much from your expertise!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors