transformation rows into columns

[alsolok]

Hello everyone,

I am working on an Excel file with rows that have sub-rows, I would like to do a transformation by bringing these sub-rows into columns.
Each month we will have a file of this format in the sharppoint.https://www.dropbox.com/scl/fi/rwe9m0fm236uyly9njp64/data.xlsx?rlkey=95pkam4g7qw72jd1q8asanepy&dl=0 

help me pleaz

[spinfuzer]

Copy and paste the entire code for example.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tZNNbsIwEIWvYmU9Sj2Of+IlCFA3lIqqK8QigqQgpYoELRJH4hxcrAHPJFHUHWGR5Mlv/I3nWVmtIi8+zmWZiZcIounnsn4LehCMtjFiZ6m3jFLGUkZrWEXjt5l4jDCrDkW2/3kM4sV4V53zzjTobx5EVskgEMGlrMk0bHpIGw+8ZFk3Su78+WL6JHJ//GHpvs5IjLNdmwvjE6o1YB3DaIUxGhqJgFxVt7YBPpq8PwO7vF6+9vkzyPc0XrPtuY3DhppUGUZ4Q/vIci1dsXRcgyZGE8KYL0bDU3tZDMb1uk5inn1n2+q389MQ1nofm5tIQHsXJFmGWQoarNNUozBO3J1/zMtCHPPDab9pTy9DldO0L+EbIqNlO+rm6VvPa9N/f5chmJP81PB02Ga5KgFUNJ3uE0Ez0xpFCcg4tZ2bE0U47/WSD9xi/Qc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Prenom / Projet / facturation" = _t, Devise = _t, rec_quantité = _t, rec_montant = _t, rec_total = _t, dep_quantité = _t, dep_montant = _t, dep_total = _t, marge = _t, taux = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Prenom / Projet / facturation", type text}, {"Devise", type text}, {"rec_quantité", type number}, {"rec_montant", type number}, {"rec_total", type number}, {"dep_quantité", type number}, {"dep_montant", Int64.Type}, {"dep_total", Int64.Type}, {"marge", type text}, {"taux", type number}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if not Text.EndsWith([#"Prenom / Projet / facturation"],"/") then null else Text.BeforeDelimiter([#"Prenom / Projet / facturation"], "/")),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each not Text.Contains([#"Prenom / Projet / facturation"], "/")),
    #"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows",{"Custom", "Prenom / Projet / facturation", "Devise", "rec_quantité", "rec_montant", "rec_total", "dep_quantité", "dep_montant", "dep_total", "marge", "taux"}),
    #"Added Index" = Table.AddIndexColumn(#"Reordered Columns", "Index", 0, 1, Int64.Type),
    #"Calculated Modulo" = Table.TransformColumns(#"Added Index", {{"Index", each Number.Mod(_, 2), type number}}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Calculated Modulo", {{"Index", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Calculated Modulo", {{"Index", type text}}, "en-US")[Index]), "Index", "Prenom / Projet / facturation"),
    #"Reordered Columns1" = Table.ReorderColumns(#"Pivoted Column",{"Custom", "0", "1", "Devise", "rec_quantité", "rec_montant", "rec_total", "dep_quantité", "dep_montant", "dep_total", "marge", "taux"})
in
    #"Reordered Columns1"

[alsolok]

Hello @spinfuzer,

Thanks you.

But, the sub rows contains "/" is excluded. 

Is it possible not to exclude sublines that contain "/"

thanks

https://www.dropbox.com/scl/fi/rwe9m0fm236uyly9njp64/data.xlsx?rlkey=95pkam4g7qw72jd1q8asanepy&dl=0 

[spinfuzer]

Hello @alsolok ,

DId you follow the entires set of steps?  We purposely did not include the other rows because we are trying to do a fill down on the rows with "/".

1) Copy only the first rows to another column (in this case we use ends with "/" otherwise null)

2) Fill down those rows

3) Filter out the first rows

4) Create index starting from 0.

5) Transform index modulo 2.

6) Pivot on Index and do not aggregate on the "Prenom / Projet / facturation" column.

7) rename and reorder your columns as desired.

[ronrsnfld]

What would you like your final result to look like from your posted data?

[alsolok]

I want this result.

This is the link for the data 

https://www.dropbox.com/scl/fi/rwe9m0fm236uyly9njp64/data.xlsx?rlkey=95pkam4g7qw72jd1q8asanepy&dl=0 

[v-xinruzhu-msft]

Hi @alsolok 

You can put the following code to advanced editor in power query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tZNNbsIwEIWvYmU9Sj2Of+IlCFA3lIqqK8QigqQgpYoELRJH4hxcrAHPJFHUHWGR5Mlv/I3nWVmtIi8+zmWZiZcIounnsn4LehCMtjFiZ6m3jFLGUkZrWEXjt5l4jDCrDkW2/3kM4sV4V53zzjTobx5EVskgEMGlrMk0bHpIGw+8ZFk3Su78+WL6JHJ//GHpvs5IjLNdmwvjE6o1YB3DaIUxGhqJgFxVt7YBPpq8PwO7vF6+9vkzyPc0XrPtuY3DhppUGUZ4Q/vIci1dsXRcgyZGE8KYL0bDU3tZDMb1uk5inn1n2+q389MQ1nofm5tIQHsXJFmGWQoarNNUozBO3J1/zMtCHPPDab9pTy9DldO0L+EbIqNlO+rm6VvPa9N/f5chmJP81PB02Ga5KgFUNJ3uE0Ez0xpFCcg4tZ2bE0U47/WSD9xi/Qc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Prenom / Projet / facturation" = _t, Devise = _t, rec_quantité = _t, rec_montant = _t, rec_total = _t, dep_quantité = _t, dep_montant = _t, dep_total = _t, marge = _t, taux = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Prenom / Projet / facturation", type text}, {"Devise", type text}, {"rec_quantité", type number}, {"rec_montant", type number}, {"rec_total", type number}, {"dep_quantité", type number}, {"dep_montant", Int64.Type}, {"dep_total", Int64.Type}, {"marge", type number}, {"taux", type number}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if Text.Contains([#"Prenom / Projet / facturation"],"/") then Table.RowCount(Table.SelectRows(#"Added Index",(x)=>x[Index]<=[Index] and Text.Contains(x[#"Prenom / Projet / facturation"],"/"))) else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
    #"Added Custom1" = Table.AddColumn(#"Filled Down", "Custom.1", each let 
 a=List.Max(Table.SelectRows(#"Filled Down",(x)=>x[Index]<=[Index] and Text.Contains(x[#"Prenom / Projet / facturation"],"/"))[Index])
  in if [Index]>=a and [Index] <a+3 then [Custom] else [Custom]+0.5),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.2", each let a=Table.SelectRows(#"Added Custom1",(x)=>x[Custom.1]=[Custom.1])[#"Prenom / Projet / facturation"],
b= if [Custom.1]-Number.RoundDown([Custom.1])=0.5 then List.Max(Table.SelectRows(#"Added Custom1",(x)=>x[Index]<[Index] and Text.Contains(x[#"Prenom / Projet / facturation"],"/"))[Index]) else null,
c=List.Max(Table.SelectRows(#"Added Custom1",(x)=>x[Index]=b)[#"Prenom / Projet / facturation"])
in Text.Combine({c,Text.Combine(a,"/")},"/")),
    #"Replaced Value" = Table.ReplaceValue(#"Added Custom2","//","/",Replacer.ReplaceText,{"Custom.2"}),
    #"Added Custom3" = Table.AddColumn(#"Replaced Value", "Custom.3", each Number.Mod([Index],3)),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom3", each ([Custom.3] <> 1)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Prenom / Projet / facturation", "Index", "Custom", "Custom.1", "Custom.3"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Custom.2", "Devise", "rec_quantité", "rec_montant", "rec_total", "dep_quantité", "dep_montant", "dep_total", "marge", "taux"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Custom.2", "Prenom / Projet / facturation"}}),
    #"Removed Duplicates" = Table.Distinct(#"Renamed Columns", {"Prenom / Projet / facturation"})
in
    #"Removed Duplicates"

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.