question

EricLacroix-3567 avatar image
0 Votes"
EricLacroix-3567 asked ·

PowerQuery Excel vs PowerBI

Hello PowerQuery experts,

I'm trying to migrate a PowerQuery formula from Power BI PowerQuery to Excel PowerQuery, I was expecting that both PowerQuery implementations were the same but unfortunately, it is either not the case or I fall on some localization issues (French vs English) please found the partial erroneous query and the error message I'm getting in Excel. I guess is about some immutability issue but unclear has I'm no way expert of Power Query.

     = Table.Pivot(#"Expanded ""tags"" from Records", List.Distinct(#"Expanded ""tags"" from Records"[name]), "name", "value") 
     Expression.Error: Sorry ... We were unable to apply the < operator to the List and List types. 
      Details : 
          Operator=< 
          Left=[List] 
          Right=[List] 

Here the full code excluding APIs Keys pointing to the faultive line: https://gist.github.com/EricLacroix/e6313e893f3f107f22eccfb79cddadb7#file-gistfile1-txt-L81


power-query-desktoppower-query-m
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

Lz-3068 avatar image
0 Votes"
Lz-3068 answered ·

Hi @EricLacroix-3567

Not quite sure it's realistic to help if you're not a PQ man and you really can't tempo. share your API keys
However, line 81 in your complete query code (reformatted below)

 #"Pivoted ""tags"" Column" = Table.Pivot(#"Expanded ""tags"" from Records",
    List.Distinct(#"Expanded ""tags"" from Records"[name]),
    "name", "value"
 )

says you're not aggregating [value] when pivoting. So, IF your [name] column already consists of unique/distinct values (probably not the case) you can remove the List.Distinct(...) part:

 #"Pivoted ""tags"" Column" = Table.Pivot(#"Expanded ""tags"" from Records",
     #"Expanded ""tags"" from Records"[name],
     "name", "value"
 )

Hope this makes helps a bit


demo.png (39.2 KiB)
·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.