Parse a table from Jira in Power Query

[dandelion]

Hi,

I have a table with this format:

Pool	Number
Fruits	5
Veggies	30
Dairy	-
grain	2
eggs	-

In Power query I've got the format below:

||Pool||Number|| |Fruits|5| |Veggies|30| |Dairy|-| |grain|2| |eggs|-|

I would like to have new columns with names Fruits, Veggies, Dairy, grain, eggs and add the values mentioned in table to these columns. 

How can I parse it efficiently?

[shafiz_p · Accepted solution]

Hi @dandelion  To acheive your desired table, first split the column by delimeter "|". Then remove all the unnecessary column which is blank. After that,  merge all the pool seperated by "|" and merget all the number seperated by "|" (Keep orginal serial). Then tranpose, split and transpose again. Use first row as header and you are done. See images:

You have format like that:

After spliting by "|", You will get blank columns:

After removing blank column:

After merged all the pools and numbers, seperated by "|", you will get only 2 columns:

After Transpose, you will get:

After split by "|", and you will get:

Now transpose back, and you will get:

After promoting first row as header, you will get your desired result:

Check the steps, in the attached excel file.

Hope this helps!!

If this solved your problem, please accept it as a solution and a kudos!!

Best Regards,
Shahariar Hafiz

[v-pnaroju-msft]

Thank you @shafiz_p and @dufoq3 for your response.

Hi @dandelion,

We would like to inquire if the solution offered by @shafiz_p and @dufoq3has resolved your issue. If you have discovered an alternative approach, we encourage you to share it with the community to assist others facing similar challenges.

Should you find the response helpful, please mark it as the accepted solution and add kudos. This recognition benefits other members seeking solutions to related queries.

Thank you.

[dufoq3 · Accepted solution]

Hi @dandelion, another solution:

Output

let
    Source = #table(null, {{"|Pool||Number|| |Fruits|5| |Veggies|30| |Dairy|-| |grain|2| |eggs|-|"}}),
    Transformed = Table.Combine(Table.TransformColumns(Source,{{"Column1", each Table.PromoteHeaders(Table.FromRows(List.Split(List.ReplaceMatchingItems(List.Select(Text.Split(_, "|"), (x)=> Text.Trim(x) <> ""), {{"-", null}}), 2))), type text}})[Column1])
in
    Transformed

[shafiz_p · Accepted solution]

Hi @dandelion  To acheive your desired table, first split the column by delimeter "|". Then remove all the unnecessary column which is blank. After that,  merge all the pool seperated by "|" and merget all the number seperated by "|" (Keep orginal serial). Then tranpose, split and transpose again. Use first row as header and you are done. See images:

You have format like that:

After spliting by "|", You will get blank columns:

After removing blank column:

After merged all the pools and numbers, seperated by "|", you will get only 2 columns:

After Transpose, you will get:

After split by "|", and you will get:

Now transpose back, and you will get:

After promoting first row as header, you will get your desired result:

Check the steps, in the attached excel file.

Hope this helps!!

If this solved your problem, please accept it as a solution and a kudos!!

Best Regards,
Shahariar Hafiz