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 below.

 

Any help would be really appreciated.

 

Please just drop any tips if you don't think of any proper solution. 

 

Thank You

 

 

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"

     

     



4 Replies

  • 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"