Pivoting Key Value pairs data

[nicm-cc]

I have SQL data which I am reading into PowerBI. The data has a column 'Hits' which has a list of key value pairs in each row. 
I need to extract the key-value pairs to unique columns based on the key.

Currently I have this M code which does the job:

let
Source = Sql.Database("xxxxx.database.windows.net", "CCAnalytics", [Query="SELECT * FROM CallPhraseHits cph INNER JOIN Calls c ON cph.CallId = c.CallId WHERE c.CustomerId = '" & Text.From(DataSource) & "';"]),

// Add a custom step to decompress the "Hits" column and convert it to a table
#"Decompress Data" = Table.TransformColumns(Source, {{"Hits", each Text.FromBinary(Binary.Decompress(_, Compression.GZip)), type text}}),

//Split the KeyValuePairColumn into a row for each Key-Value pair
#"Split into rows" = Table.ExpandListColumn(Table.TransformColumns(#"Decompress Data", {{"Hits", Splitter.SplitTextByDelimiter(",", QuoteStyle.None), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Hits"),

//Replace Unused Characters
#"Replaced Value" = Table.ReplaceValue(#"Split into rows","[{","",Replacer.ReplaceText,{"Hits"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value"," {","",Replacer.ReplaceText,{"Hits"}),
#"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","}]","",Replacer.ReplaceText,{"Hits"}),
#"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","}","",Replacer.ReplaceText,{"Hits"}),

//Split Key and Value into separate columns
#"Split KeyValue" = Table.SplitColumn(#"Replaced Value3", "Hits", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Key", "Value"}),

// Format Key Value Columns
#"Changed Decimal" = Table.ReplaceValue(#"Split KeyValue",".",",",Replacer.ReplaceText,{"Value"}),
// Format Value Columns
#"Convert to Number" = Table.TransformColumnTypes(#"Changed Decimal",{{"Key", type text}, {"Value", type number}}),

//Pivot Key into column-name and Value into its value
PivotColumn = Table.Pivot(#"Convert to Number", List.Distinct(#"Convert to Number"[Key]), "Key", "Value"),


in 

Pivot Column

This works fine for small datasets but anything larger than a couple thousand rows basically becomes unusable. The bottleneck is definitely the Table.Pivot operation. 
Is there a better way to dynamically extract the Key-value pairs into columns?
P.S this is an embedded powerbi solution so I cannot make use of Scripts (Python or R) which would have been ideal.

[rubayatyasmin]

Hi, @nicm-cc 

how about adding table.buffer() before pivoting the entire table of split the tables into several chunks then perform the pivot then append the result? Though it's a workaround that you will have to do but it may solve your problem. 

check the #"Convert to Number" step supports query folding. Query folding.

Also, is it possible to extract the key value pair in the sql source? if so then give it a shot. 

[nicm-cc]

Hi rubayatyasmin,

The query does not support query folding as it uses an advanced sql select statement.

I am working on trying to extract a select view from the source itself but this has not proved easy.