Forum Discussion

RanjeetJha's avatar
RanjeetJha
Regular Visitor
2 years ago
Solved

Power BI

HI Everyone,

Can you please help me. I want to achieve data as per output table.

 

NameSkill
GirishPower BI Developer
ShrikantMSBI Developer
GirishTableau Developer
GirishLooker Developer
SrikanthPower BI Developer

 

Output I want:

GirishPower BI, Tableau, Looker
SrikanthMSBI Developer, Power BI
Need Help
  • lbendlin's avatar
    lbendlin
    2 years ago
    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs8syizOUNJRCsgvTy1ScPJUcEktS83JL0gtUorViVYKLsrMTswrAanwDcaQhesOSUzKSU0sxSXtk5+fDTQdp9HYLI8FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Skill = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source," Developer","",Replacer.ReplaceText,{"Skill"}),
    #"Grouped Rows" = Table.Group(#"Replaced Value", {"Name"}, {{"Skills", each _[Skill]}}),
    #"Extracted Values" = Table.TransformColumns(#"Grouped Rows", {"Skills", each Text.Combine(List.Transform(_, Text.From), ","), type text})
in
    #"Extracted Values"

    How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".

4 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago
    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs8syizOUNJRCsgvTy1ScPJUcEktS83JL0gtUorViVYKLsrMTswrAanwDcaQhesOSUzKSU0sxSXtk5+fDTQdp9HYLI8FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Skill = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source," Developer","",Replacer.ReplaceText,{"Skill"}),
    #"Grouped Rows" = Table.Group(#"Replaced Value", {"Name"}, {{"Skills", each _[Skill]}}),
    #"Extracted Values" = Table.TransformColumns(#"Grouped Rows", {"Skills", each Text.Combine(List.Transform(_, Text.From), ","), type text})
in
    #"Extracted Values"

    How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".

    • Anuja_Chaudhari's avatar
      Anuja_Chaudhari
      Helper I
      2 years ago

       

       

      2 nd approch 

       

      let

      Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs8syizOUNJRCsgvTy1ScPJUcEktS83JL0gtUorViVYKzijKzE7MKwGq8A3GkIXrDklMyklNLMUl7ZOfnw00HafR2CyPBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Skill = _t]),

      GroupedTable = Table.Group(Source, {"Name"}, {{"Skills", each Text.Combine(List.Distinct([Skill]), ", "), type text}})


      in

      GroupedTable

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    2 years ago

    Hi,

    This M code works

    let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Replaced Value" = Table.ReplaceValue(Source,"Developer","",Replacer.ReplaceText,{"Skill"}),
    #"Grouped Rows" = Table.Group(#"Replaced Value", {"Name"}, {{"Skills", each Text.Combine([Skill],","), type text}})
in
    #"Grouped Rows"

    Hope this helps.