Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Dear All,
I use software that allows me to export accounting data as on (Table 1)
On the Label column of the operation, when the label is very long, during the data export, Excel automatically returns part of the label to the next line, which is not really to my taste, but I don't I have no power over this, however I would like to know if there is a manipulation in Excel using Power query that can allow this new line to be returned to the initial line as below like in (Table 2)
Solved! Go to Solution.
Hey Cheloo,
Here's an approach you can try. First I want to create a unique ID for each row. To do that:
You can copy paste the below script into the advanced editor to see all steps in action.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZDNboMwEIRfxeIcS14bG7gmaU79Uzn0EOXgkhVYTU1loBJ5+hpMqatGWtmalfab2T0eE844owwoh2TzK7gXTxVq638hhH99Kalk6is5bZY5AMrSn7lJSC8e2sH22ljyjPq9mwF8JQhQUqqZEFrlYO1I9noMcqo/+CLGT+K1Md0nOmNrsnWIV/Q94GmYlcrzQYGMHO5s1Wjb45kcWodd/8/IgxUFGYyCyLy410PdTDYv5gtdcFn2kFyAVAIilz126HpSanvubqzCcgr5emEvivlSrb2YnmxRV81ssPCLvMizCL5rryPZ6Tdjb4UXHriGFxTYFEfbasp+uOgP7GJ2nqUQBy977dxIHk3dxKc5fQM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Valeur = _t, #"Libelle de l'operation" = _t, Debit = _t, credit = _t, solde = _t]),
Mydata = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Valeur", type date}, {"Debit", Int64.Type}, {"solde", Int64.Type}, {"credit", Int64.Type}}),
RemoveNull = Table.SelectRows ( Mydata, each [Date] <> null ),
AddIndex = Table.AddIndexColumn ( RemoveNull, "Index", 0, 1, Int64.Type ),
Show_Mydata_Step = Mydata,
Merge_AddIndexStep =
Table.NestedJoin (
Show_Mydata_Step, Table.ColumnNames ( Show_Mydata_Step ),
AddIndex, Table.ColumnNames ( Show_Mydata_Step ),
"Custom1", JoinKind.LeftOuter
),
ExpandIndex =
Table.ExpandTableColumn (
Merge_AddIndexStep,
"Custom1",
{ "Index" },
{ "Index" }
),
FillDown = Table.FillDown ( ExpandIndex, { "Date", "Valeur", "Index" } ),
GroupRows =
Table.Group (
FillDown,
{ "Date", "Valeur", "Index" },
{
{
"Libelle de l'operation",
each Text.Combine ( [#"Libelle de l'operation"], " " ),
type nullable text
},
{ "Debit", each List.Sum ( [Debit] ), type nullable number },
{ "credit", each List.Sum ( [credit] ), type nullable text },
{ "solde", each List.Sum ( [solde] ), type nullable number }
}
)
in
GroupRows
Let me know if this solves your issue, hope it helps!
Rick
--------------------------------------------------
@ me in replies or I'll lose your thread
Master Power Query M? -> https://powerquery.how
Read in-depth articles? -> BI Gorilla
Youtube Channel: BI Gorilla
If this post helps, then please consider accepting it as the solution to help other members find it more quickly.
Hey Cheloo,
Here's an approach you can try. First I want to create a unique ID for each row. To do that:
You can copy paste the below script into the advanced editor to see all steps in action.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZDNboMwEIRfxeIcS14bG7gmaU79Uzn0EOXgkhVYTU1loBJ5+hpMqatGWtmalfab2T0eE844owwoh2TzK7gXTxVq638hhH99Kalk6is5bZY5AMrSn7lJSC8e2sH22ljyjPq9mwF8JQhQUqqZEFrlYO1I9noMcqo/+CLGT+K1Md0nOmNrsnWIV/Q94GmYlcrzQYGMHO5s1Wjb45kcWodd/8/IgxUFGYyCyLy410PdTDYv5gtdcFn2kFyAVAIilz126HpSanvubqzCcgr5emEvivlSrb2YnmxRV81ssPCLvMizCL5rryPZ6Tdjb4UXHriGFxTYFEfbasp+uOgP7GJ2nqUQBy977dxIHk3dxKc5fQM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Valeur = _t, #"Libelle de l'operation" = _t, Debit = _t, credit = _t, solde = _t]),
Mydata = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Valeur", type date}, {"Debit", Int64.Type}, {"solde", Int64.Type}, {"credit", Int64.Type}}),
RemoveNull = Table.SelectRows ( Mydata, each [Date] <> null ),
AddIndex = Table.AddIndexColumn ( RemoveNull, "Index", 0, 1, Int64.Type ),
Show_Mydata_Step = Mydata,
Merge_AddIndexStep =
Table.NestedJoin (
Show_Mydata_Step, Table.ColumnNames ( Show_Mydata_Step ),
AddIndex, Table.ColumnNames ( Show_Mydata_Step ),
"Custom1", JoinKind.LeftOuter
),
ExpandIndex =
Table.ExpandTableColumn (
Merge_AddIndexStep,
"Custom1",
{ "Index" },
{ "Index" }
),
FillDown = Table.FillDown ( ExpandIndex, { "Date", "Valeur", "Index" } ),
GroupRows =
Table.Group (
FillDown,
{ "Date", "Valeur", "Index" },
{
{
"Libelle de l'operation",
each Text.Combine ( [#"Libelle de l'operation"], " " ),
type nullable text
},
{ "Debit", each List.Sum ( [Debit] ), type nullable number },
{ "credit", each List.Sum ( [credit] ), type nullable text },
{ "solde", each List.Sum ( [solde] ), type nullable number }
}
)
in
GroupRows
Let me know if this solves your issue, hope it helps!
Rick
--------------------------------------------------
@ me in replies or I'll lose your thread
Master Power Query M? -> https://powerquery.how
Read in-depth articles? -> BI Gorilla
Youtube Channel: BI Gorilla
If this post helps, then please consider accepting it as the solution to help other members find it more quickly.
NewStep=Table.Group(Table1,"Date",List.Transform(List.Skip(Table.ColumnNames(Table1)),(x)=>{x,each let a=Table.Column(_,x) in if a{0} is text then Text.Combine(a," ") else a{0}}),0,(x,y)=>Byte.From(y<>null))
Thanks so much
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.