help to transform a table on Powerquerry

[jps_HHH]

I have transformed a table from excel file (throught powerquerry) to a unique table to be used in PBI. 

At the moment the table contains:

Column 1            Column 2      Column 3           Column 4      Column 5            Column 6      Column 7           Column 8 
fiscal year 25       Site 1            April                    50 sells         fiscar year 24       Site 2             April                      46 sells  
fiscal year 25       Site 1            May                    20 sells          fiscar year 24       Site 2             May                      33 sells  
fiscal year 25       Site 1            June                    15 sells          fiscar year 24       Site 2             June                     55 sells  
fiscal year 25       Site 1             July                     40 sells         fiscar year 24       Site 2              July                     23 sells  
fiscal year 25       Site 1             August               21 sells          fiscar year 24        Site 2            August                 23 sells
fiscal year 25       Site 1              September        24 sells           fiscar year 24      Site 2            September           29 sells

and I want to combine "column 1 with column 5" and "column 2 and column 6" and "column 3 with column 7" and "column 4 with column 8"  , like : 

Column 1            Column 2      Column 3           Column 4    
fiscal year 25       Site 1            April                    50 sells         
fiscal year 25       Site 1            May                    20 sells         
fiscal year 25       Site 1            June                    15 sells          
fiscal year 25       Site 1             July                     40 sells          
fiscal year 25       Site 1             August               21 sells         
fiscar year 24       Site 2             April                  46 sells  
fiscar year 24       Site 2             May                   33 sells  
fiscar year 24       Site 2             June                  55 sells  
fiscar year 24       Site 2              July                  23 sells  
fiscar year 24        Site 2            August              23 sells

Could you help me ?

[v-achippa]

Hi @jps_HHH,

Thank you for reaching out to Microsoft Fabric Community.

To combine the columns and get the expected output, please follow below steps:

• Open Power bi desktop and load the table.
• Open the Power Query Editor and go to the Advanced Editor
  

• Replace the existig code with the below code and click on Done:
  
  

  let
  Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSsssTk7MUahMTSxSMDJV0lEKzixJVTAEMhwLijJzgLSpgUJxak5OMZAJVlwEVWwCU2yEpNjEDKo4Vgef2b6JlUDSiBiTIUqNjYky16s0LxVIGZoSYTBUrakpkSbngNxhQoyToWqNiHOzY2l6aXEJSL0hMQENV02c6cGpBSWpuUmpRSAtJkRYgKLBEmZHLAA=", BinaryEncoding.Base64), Compression.Deflate)),
  let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Column 1" = _t, #"Column 2" = _t, #"Column 3" = _t, #"Column 4" = _t, #"Column 5" = _t, #"Column 6" = _t, #"Column 7" = _t, #"Column 8" = _t]),
  #"Changed Type" = Table.TransformColumnTypes(Source, List.Transform(Table.ColumnNames(Source), each {_, type text})),
  FirstHalf = Table.SelectColumns(#"Changed Type", {"Column 1", "Column 2", "Column 3", "Column 4"}),
  SecondHalf = Table.SelectColumns(#"Changed Type", {"Column 5", "Column 6", "Column 7", "Column 8"}),
  RenamedSecond = Table.RenameColumns(SecondHalf, {{"Column 5", "Column 1"}, {"Column 6", "Column 2"}, {"Column 7", "Column 3"}, {"Column 8", "Column 4"}}),
  Combined = Table.Combine({FirstHalf, RenamedSecond}) in Combined

And the output will be like below:

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thanks and regards,

Anjan Kumar Chippa

[v-achippa]

Hi @jps_HHH 

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution I have provided for the issue worked? or let us know if you need any further assistance.
If my response addressed, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Thanks and regards,

Anjan Kumar Chippa

[jps_HHH · Accepted solution]

I need further assistance since I've made more changes at the file. So It is not enough to copy the code and paste it (because it will clean all previous steps done by me).

[v-achippa]

Hi @jps_HHH,

Is your issue resolved? What changes have you made and what exact assistance you need from our side? please question completely what is the issue and explain in detail

Thanks and regards,

Anjan Kumar Chippa

[v-achippa]

Hi @jps_HHH 

As we haven’t heard back from you, we wanted to kindly follow up to check Is your issue resolved 
If any response addressed, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Thanks and regards,

Anjan Kumar Chippa

[jps_HHH]

I didnt understand how to manage this code. 
In the link below there is similar file in excel that I need to tranform it 

https://limewire.com/d/o7fqo#8FhhUIkPZr 

[SundarRaj]

@jps_HHH , Have a look at this file. Is this what you are looking for? 
https://docs.google.com/spreadsheets/d/1bUhF-MBUL661hxi4soJP8G4sWDdSc1tF/export?format=xlsx&ouid=104...

[jps_HHH]

yes, that is what i'looking for

[SundarRaj]

Hi @jps_HHH , another solution for you to look at. Thanks. Also, what would I mention is, that if you can possibly provide static column names to it rather than Column1, it would be great since it would allow you to split automatically without manually typing in the number 4. It'll pick up the distinct count of columns and use that. Here, I have given certain names to the columns for you reference.

Here's the code:

let
Source = Excel.CurrentWorkbook(){[Name="Table6"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}}),
ColNames = List.Distinct ( Record.ToList( #"Changed Type"{0} ) ),
Number = List.Count ( ColNames ),
Splitting = List.Split ( Table.ToColumns ( #"Changed Type" ) , Number ),
Table = List.Transform ( Splitting , each Table.PromoteHeaders ( Table.FromColumns ( _ ) ) ),
Combined = Table.Combine ( Table )
in
Combined

[wdx223_Daniel]

NewStep=#table(List.FirstN(Table.ColumnNames(YourTable),4),List.TransformMany(Table.ToRows(YourTable),each List.Split(_,4),(x,y)=>y))

[Omid_Motamedise]

Hi @jps_HHH 

copy the following code and past it into the advanceeditor.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSsssTk7MqUxNLDIyVdJRCs4sSTUE0o4FRZk5QNrUoDg1J6cYyAIrLAIrNIEqNEJSaGIGURirg9NM38RKIGlEyESIMmNjguZ5lealAilDUwIGQtWZmhJhYg7IbhNCToSqMyLsRsfS9NLiEpBaQ0IBCVcJMzUWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t]),
    #"Transposed Table" = Table.Transpose(Source),
    Custom1 = Table.Combine(List.Transform(Table.Split(#"Transposed Table",4),Table.Transpose))
in
    Custom1

[slorin]

Hi @jps_HHH 

= Table.Combine(
    List.Transform(
      Table.ToRows(Your_Source), 
      each Table.FromRows(List.Split(_,4))))

or

= Table.FromRows(
    List.Combine(
     Table.ToList(
       Your_Source, 
       each List.Split(_,4))))

Stéphane

[jps_HHH]

Where do I place this code ?