Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Need help in transforming the table.

Hello,   I am in the learning phase of the Power BI. I have been struggling on one problem for last three days. I have this below table.    I need to transform this into table looking like ...
Need Help
Tips and Tricks
  • Fowmy's avatar
    Fowmy
    5 years ago

    Anonymous 
    Paste this code in a New Blank Query in the Advanced Editor and check the steps. All using GUI.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Pcw7C4QwEATgv7JsbWOCsfbR2MhxbUgRNHAH9wARMfvrdQJJkWHYj4y1/PBb+O30DMt/W2kaKXLFw+v9WfNt9t9w30a/B6pzUbloOthVliHd/U6UiJC6QA/Al4gQVWAAaABCdAKVpxoAQpoCacoAEMoUSFMtACEtO3cB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t, #"(blank).4" = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Merged Columns" = Table.CombineColumns(#"Promoted Headers",{"Date 1", "Date 2", "Date 3 v"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"Merged"),
    #"Pivoted Column" = Table.Pivot(#"Merged Columns", List.Distinct(#"Merged Columns"[#"Child Record Name"]), "Child Record Name", "Merged"),
    #"Merged Columns1" = Table.CombineColumns(#"Pivoted Column",{"A", "B", "C"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"Merged"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Merged Columns1", "Merged", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Merged.1", "Merged.2", "Merged.3", "Merged.4", "Merged.5", "Merged.6", "Merged.7", "Merged.8", "Merged.9"})
in
    #"Split Column by Delimiter"

     

     



Fowmy's avatar
Fowmy
Icon for Super User rankSuper User
5 years ago

Anonymous 
Paste this code in a New Blank Query in the Advanced Editor and check the steps. All using GUI.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Pcw7C4QwEATgv7JsbWOCsfbR2MhxbUgRNHAH9wARMfvrdQJJkWHYj4y1/PBb+O30DMt/W2kaKXLFw+v9WfNt9t9w30a/B6pzUbloOthVliHd/U6UiJC6QA/Al4gQVWAAaABCdAKVpxoAQpoCacoAEMoUSFMtACEtO3cB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t, #"(blank).4" = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Merged Columns" = Table.CombineColumns(#"Promoted Headers",{"Date 1", "Date 2", "Date 3 v"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"Merged"),
    #"Pivoted Column" = Table.Pivot(#"Merged Columns", List.Distinct(#"Merged Columns"[#"Child Record Name"]), "Child Record Name", "Merged"),
    #"Merged Columns1" = Table.CombineColumns(#"Pivoted Column",{"A", "B", "C"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"Merged"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Merged Columns1", "Merged", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Merged.1", "Merged.2", "Merged.3", "Merged.4", "Merged.5", "Merged.6", "Merged.7", "Merged.8", "Merged.9"})
in
    #"Split Column by Delimiter"

 

 



  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    You made my day. Thank you so much.

     

    Is there any way where we can retain the column name?

    • Fowmy's avatar
      Fowmy
      Icon for Super User rankSuper User
      5 years ago

      Anonymous 

      Please find below the code with the Column Names renamed as desired:

       

      let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "PYw7CoAwEESvEra2SUSt/TQ2IrYhRTABBT8gFrqn11GSYh/DPHa0pt4efjvF4Mf9cKJtKKF6mhcXms6u/u0ae3ohQ1AhpGQSTejL9y6EG2AZRQWBhxtgFUUNkUIA/E+pMJVBAJxF8U3lEIDKo/imCgiACzLmAQ==",
          BinaryEncoding.Base64
        ),
        Compression.Deflate
      )
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [
        #"(blank)" = _t,
        #"(blank).1" = _t,
        #"(blank).2" = _t,
        #"(blank).3" = _t,
        #"(blank).4" = _t
      ]
  ),
  #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars = true]),
  #"Merged Columns" = Table.CombineColumns(
    #"Promoted Headers",
    {"Date 1", "Date 2", "Date 3"},
    Combiner.CombineTextByDelimiter("|", QuoteStyle.None),
    "Merged"
  ),
  #"Pivoted Column" = Table.Pivot(
    #"Merged Columns",
    List.Distinct(#"Merged Columns"[#"Child Record Name"]),
    "Child Record Name",
    "Merged"
  ),
  #"Merged Columns1" = Table.CombineColumns(
    #"Pivoted Column",
    {"A", "B", "C"},
    Combiner.CombineTextByDelimiter("|", QuoteStyle.None),
    "Merged"
  ),
  Step1 = Table.SplitColumn(
    #"Merged Columns1",
    "Merged",
    Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv),
    {
      "Merged.1",
      "Merged.2",
      "Merged.3",
      "Merged.4",
      "Merged.5",
      "Merged.6",
      "Merged.7",
      "Merged.8",
      "Merged.9"
    }
  ),
  Custom2 = Table.ExpandListColumn(
    Table.AddColumn(
      Table.FromList(
        List.Distinct(#"Promoted Headers"[Child Record Name]),
        Splitter.SplitByNothing(),
        null,
        null,
        ExtraValues.Error
      ),
      "Custom",
      each List.Skip(Table.ColumnNames(#"Promoted Headers"), 2)
    ),
    "Custom"
  ),
  NewHeaderList = Table.AddColumn(Custom2, "NewHeader", each [Column1] & " (" & [Custom] & ")")[
    NewHeader
  ],
  OldHeaderList = List.Skip(Table.ColumnNames(Step1), 1),
  Step3 = Table.ToColumns(
    Table.Transpose(Table.FromColumns({OldHeaderList, NewHeaderList}, {"Old", "New"}))
  ),
  #"Renamed Columns" = Table.RenameColumns(Step1, Step3)
in
  #"Renamed Columns"

       

       

       

    • Fowmy's avatar
      Fowmy
      Icon for Super User rankSuper User
      5 years ago

      Anonymous 

       

      I will see that when I have time for sure.