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.
Hi, I want to clean this table in such a way that I can make reports.
Here you can see dates in columns 1 and 7 with the same titles in between.
Is there any way where I can stack these columns in a format of
columns 1 to 6
Columns 7 to 12
Columns 13 to 18
on top of each other?
I believe merge / append queries don't work for this: Happy for suggestions
Solved! Go to Solution.
Hi @koiralasmn, for future requests: provide sample date as table so we can copy/paste. Thanks.
Before:
After:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtJRMjFVyM3MU0itALKDUssU0vMTc4qBbDMDsHhJUWJmXmZeOlDEyEDBJ780MxWsMDFFwdAAyBp4E2J1opUs9A31jQyMjIDCbo4+wa5E0JYILSFBofhUQqUNDQjqgAqDKYizjEh3lhFxlsC4QGeRaAnEZcaku8yYZJcR0oGiMzYWAA==", 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, Column9 = _t, Column10 = _t, Column11 = _t, Column12 = _t, Column13 = _t, Column14 = _t, Column15 = _t, Column16 = _t, Column17 = _t, Column18 = _t]),
SplitBy = [ a = List.PositionOf(Record.ToList(Source{0}), "", Occurrence.All),
b = a{1} - a{0}
][b],
StepBack = Source,
PromotedHeaders = Table.PromoteHeaders(StepBack, [PromoteAllScalars=true]),
SplitAndCombine = Table.Combine(List.Transform(List.Split(Table.ToColumns(PromotedHeaders), SplitBy), Table.FromColumns)),
RenamedColumns = Table.RenameColumns(SplitAndCombine, List.Zip({ Table.ColumnNames(SplitAndCombine), {"Date"} & List.Skip(List.FirstN(Table.ColumnNames(PromotedHeaders), SplitBy)) }))
in
RenamedColumns
Hi @koiralasmn, for future requests: provide sample date as table so we can copy/paste. Thanks.
Before:
After:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtJRMjFVyM3MU0itALKDUssU0vMTc4qBbDMDsHhJUWJmXmZeOlDEyEDBJ780MxWsMDFFwdAAyBp4E2J1opUs9A31jQyMjIDCbo4+wa5E0JYILSFBofhUQqUNDQjqgAqDKYizjEh3lhFxlsC4QGeRaAnEZcaku8yYZJcR0oGiMzYWAA==", 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, Column9 = _t, Column10 = _t, Column11 = _t, Column12 = _t, Column13 = _t, Column14 = _t, Column15 = _t, Column16 = _t, Column17 = _t, Column18 = _t]),
SplitBy = [ a = List.PositionOf(Record.ToList(Source{0}), "", Occurrence.All),
b = a{1} - a{0}
][b],
StepBack = Source,
PromotedHeaders = Table.PromoteHeaders(StepBack, [PromoteAllScalars=true]),
SplitAndCombine = Table.Combine(List.Transform(List.Split(Table.ToColumns(PromotedHeaders), SplitBy), Table.FromColumns)),
RenamedColumns = Table.RenameColumns(SplitAndCombine, List.Zip({ Table.ColumnNames(SplitAndCombine), {"Date"} & List.Skip(List.FirstN(Table.ColumnNames(PromotedHeaders), SplitBy)) }))
in
RenamedColumns
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.