Hi, I am importing multiple CSVs from a folder with a function like this:(FolderPath as text, SingleKeyword as text) => let //Open the folder ShowFiles = Folder.Files(FolderPath), //Filter by DateText KeepOnlyDateText = Table.SelectRows(ShowFiles, each Text.EndsWith([Folder Path], FolderPath) and Text.Contains([Name], SingleKeyword) = true), //Get ContentCol AddContent = Table.AddColumn(KeepOnlyDateText, "ContentTbl", each Table.PromoteHeaders(Csv.Document([Content],[Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.Csv]))) in AddContentThese CSVs have 2 columns in the end that look like this:<site-url> <language-region> Top Rank <site-url> <language-region> Top Ranking URL(see below) When expanding the columns of these CSV files, these final 2 cols will repeat for each site-url and lang-region combinations. Is there a way to tell PQ to ignore the first 2 words of each column name, and consolidate all into just 2 columns:Top Rank Top Ranking URLImkeF v-juanli-msft Nolock ?Thanks!

Hi freelensia,you can redefine the problem as removing HTML tags from a string. You can do that with the PQ function Html.Table and then take the first value of the table with Table.FirstValue.An example of using Html.Table: Html.Table("<site-url> <language-region> Top Rank", {{"text",":root"}})-----EDIT: This is WRONG, I haven't seen the image in the original post, maybe not loaded, I don't know.

Hi freelensia,there is also another option I've forgotten to mention. ImkeF has written a handy PQ query which removes HTML tags from a string: https://github.com/ImkeF/M/blob/master/Library/Text.RemoveHtmlTags.pq. -----EDIT: This is WRONG, I haven't seen the image in the original post, maybe not loaded, I don't know.

Hi freelensia,wait a sec. I think we have forgotten an important fact.We are trying to map many columns to 2. It means that we need either to remove N-2 columns OR aggregate all Top Rank columns and all Top Ranking URL columns. Quore: "Is there a way to tell PQ to ignore the first 2 words of each column name, and consolidate all into just 2 columns:" Or do I understand it wrong?If we have to aggregate, let me know what aggregation function we should use. A Sum?

Combine similar columns into one when importing multiple CSVs

11 Replies

Nolock
Resident Rockstar
7 years ago
Hi freelensia,
you can redefine the problem as removing HTML tags from a string. You can do that with the PQ function Html.Table and then take the first value of the table with Table.FirstValue.
An example of using Html.Table:

Html.Table("<site-url> <language-region> Top Rank", {{"text",":root"}})
-----
EDIT: This is WRONG, I haven't seen the image in the original post, maybe not loaded, I don't know.
- Nolock
  Resident Rockstar
  7 years ago
  Hi freelensia,
  there is also another option I've forgotten to mention.
  ImkeF has written a handy PQ query which removes HTML tags from a string: https://github.com/ImkeF/M/blob/master/Library/Text.RemoveHtmlTags.pq.
  
  -----
  EDIT: This is WRONG, I haven't seen the image in the original post, maybe not loaded, I don't know.

Mariusz

Community Champion

7 years ago

Hi freelensia

You can try function like below as an alternative to Nolock

( tbl as table) => let
    Source = Table.ColumnNames ( tbl ),
    topRank = { List.First( List.Select( Source, each Text.EndsWith( _, "Top Rank" ) ) ), "Top Rank" },
    topRankingURL = { List.First( List.Select( Source, each Text.EndsWith( _, "Top Ranking URL" ) ) ), "Top Ranking URL" },
    return = Table.RenameColumns(tbl, { topRank, topRankingURL } ) 
in
    return

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

freelensia

Advocate II

7 years ago

Thanks Mariusz . I tried your approach but I got error:

Expression.Error: We cannot convert the value "" to type Function.
Details:
    Value=
    Type=Type

See code below.

let
    GetFilterCSVsBySingleKeyword = FilterCSVsBySingleKeyword(RangeValue("ImportFolder"),"keywords_by_site"),
    ExpandContentTbl = Table.ExpandTableColumn(GetFilterCSVsBySingleKeyword, "ContentTbl", {"Keyword", "Min Volume", "Max Volume", "Difficulty", "blarlo.com en-US Top Rank", "blarlo.com en-US Top Ranking URL", "cadencetranslate.com en-US Top Rank", "cadencetranslate.com en-US Top Ranking URL", "crowdworks.jp en-US Top Rank", "crowdworks.jp en-US Top Ranking URL", "expertrans.com en-US Top Rank", "expertrans.com en-US Top Ranking URL", "expotor.com en-US Top Rank", "expotor.com en-US Top Ranking URL", "freeiva.com en-US Top Rank", "freeiva.com en-US Top Ranking URL", "freelensia.com en-US Top Rank", "freelensia.com en-US Top Ranking URL", "interpreter.com en-US Top Rank", "interpreter.com en-US Top Ranking URL", "interpreter.io en-US Top Rank", "interpreter.io en-US Top Ranking URL", "kotobaito.com en-US Top Rank", "kotobaito.com en-US Top Ranking URL", "languagers.com en-US Top Rank", "languagers.com en-US Top Ranking URL", "proz.com en-US Top Rank", "proz.com en-US Top Ranking URL", "scheduleinterpreter.com en-US Top Rank", "scheduleinterpreter.com en-US Top Ranking URL", "tanner.vn en-US Top Rank", "tanner.vn en-US Top Ranking URL", "theinterpreterdirectory.com en-US Top Rank", "theinterpreterdirectory.com en-US Top Ranking URL", "tikktalk.com en-US Top Rank", "tikktalk.com en-US Top Ranking URL", "transperfect.com en-US Top Rank", "transperfect.com en-US Top Ranking URL", "traveloco.jp en-US Top Rank", "traveloco.jp en-US Top Ranking URL", "tubudd.com en-US Top Rank", "tubudd.com en-US Top Ranking URL", "tutoroo.co en-US Top Rank", "tutoroo.co en-US Top Ranking URL", "viettranslators.com en-US Top Rank", "viettranslators.com en-US Top Ranking URL"}, {"Keyword", "Min Volume", "Max Volume", "Difficulty", "blarlo.com en-US Top Rank", "blarlo.com en-US Top Ranking URL", "cadencetranslate.com en-US Top Rank", "cadencetranslate.com en-US Top Ranking URL", "crowdworks.jp en-US Top Rank", "crowdworks.jp en-US Top Ranking URL", "expertrans.com en-US Top Rank", "expertrans.com en-US Top Ranking URL", "expotor.com en-US Top Rank", "expotor.com en-US Top Ranking URL", "freeiva.com en-US Top Rank", "freeiva.com en-US Top Ranking URL", "freelensia.com en-US Top Rank", "freelensia.com en-US Top Ranking URL", "interpreter.com en-US Top Rank", "interpreter.com en-US Top Ranking URL", "interpreter.io en-US Top Rank", "interpreter.io en-US Top Ranking URL", "kotobaito.com en-US Top Rank", "kotobaito.com en-US Top Ranking URL", "languagers.com en-US Top Rank", "languagers.com en-US Top Ranking URL", "proz.com en-US Top Rank", "proz.com en-US Top Ranking URL", "scheduleinterpreter.com en-US Top Rank", "scheduleinterpreter.com en-US Top Ranking URL", "tanner.vn en-US Top Rank", "tanner.vn en-US Top Ranking URL", "theinterpreterdirectory.com en-US Top Rank", "theinterpreterdirectory.com en-US Top Ranking URL", "tikktalk.com en-US Top Rank", "tikktalk.com en-US Top Ranking URL", "transperfect.com en-US Top Rank", "transperfect.com en-US Top Ranking URL", "traveloco.jp en-US Top Rank", "traveloco.jp en-US Top Ranking URL", "tubudd.com en-US Top Rank", "tubudd.com en-US Top Ranking URL", "tutoroo.co en-US Top Rank", "tutoroo.co en-US Top Ranking URL", "freelensia.com en-US Top Rank", "freelensia.com en-US Top Ranking URL"}),
    CallCombineTopRankCols = CombineTopRankCols(ExpandContentTbl),
    DelCols = Table.RemoveColumns(CallCombineTopRankCols,{"Content", "Name", "Extension", "Date accessed", "Date modified", "Date created", "Attributes", "Folder Path"})
in
    DelCols

Nolock can you explain in more detail how to I can use your solution in my steps above? The third step is Mariusz solution so you can kindly replace it with the code you are suggesting, it would be much appreciated.

Nolock
Resident Rockstar
7 years ago
Hi freelensia,
wait a sec. I think we have forgotten an important fact.
We are trying to map many columns to 2. It means that we need either to remove N-2 columns OR aggregate all Top Rank columns and all Top Ranking URL columns. Quore: "Is there a way to tell PQ to ignore the first 2 words of each column name, and consolidate all into just 2 columns:" Or do I understand it wrong?
If we have to aggregate, let me know what aggregation function we should use. A Sum?

freelensia

Advocate II

7 years ago

Yes tks Mariusz if you could kindly add the line in bold below to your original answer for the community's sake before I accept your solution.

let
GetFilterCSVsBySingleKeyword = FilterCSVsBySingleKeyword(RangeValue("ImportFolder"),RangeValue("KeywordsBySite")),
CallCombineTopRankCols = Table.AddColumn(GetFilterCSVsBySingleKeyword, "NewContentTbl", each CombineTopRankCols([ContentTbl])),
ExpandNewContentTbl = Table.ExpandTableColumn(CallCombineTopRankCols, "NewContentTbl", {"Top Rank", "Top Ranking URL"}, {"Top Rank", "Top Ranking URL"})
in
ExpandNewContentTbl

Forum Discussion

Combine similar columns into one when importing multiple CSVs

11 Replies

Related content

Power Query Combine Multiple Files From Folder

Tables combine

Combine values from multiple rows

Append/ Combine Multiple Json Files

RLS multiple combination