Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
bhaskarpbi999
Helper V
Helper V

Group data and create columns for weekdays and Time values

Hi All,

I need your assitance to shaep the data to the expected format.

 

Current Data Format

 

bhaskarpbi999_0-1709561115826.png

Expected result the Week days needs to be columns and Time should be like below.

 

bhaskarpbi999_1-1709561242084.png

 

 

Regards,

Bhaskar

 

1 ACCEPTED SOLUTION

dufoq3_0-1709722533558.png

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("zZXJCsIwEIZfZeg5atKKbb2JCxZcilY8VA8BFyJpKq2+v3VARIxHyVwmmZDl48vhz3NPiCCMPeYNy+qmLkrrsob1VSrTrI2mM0gHs/lyMV4z2FU7M+zAqJJnWSgjawYiYpCWug2JObQhlboozbGGJMHNr6PY4Ds483kARXO7uWv9HoS3Zy5p5sum8LDPeYtHTSWDFD+RRJcQkughEhFL2YacJUSiZWk7JmcJkWhZyqbkLCESLUuTFTlLiPTDUvTsMlmpk/w3TRD7oSXbfJcolhRxz/P5VU55LPnhnoeQH0tyuOch5MeSGe55CPmxpIV7ni8/+wc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Postal = _t, #"City Country" = _t, #"Collect Point" = _t, Date = _t, Time = _t, Index = _t]),
    FilteredRows = Table.SelectRows(Source, each ([Date] <> "null" and [Date] <> null)),
    GroupedRows = Table.Group(FilteredRows, {"Collect Point", "Date"}, {{"All", each Table.AddIndexColumn(_, "ID", 1, 1, type text), type table}}),
    CombinedAll = Table.Combine(GroupedRows[All]),
    AddedPrefix = Table.TransformColumns(CombinedAll, {{"ID", each "Time" & Text.From(_), type text}})
in
    AddedPrefix

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

View solution in original post

34 REPLIES 34

Hi, I'd like to help you, but data in the link you've provided 

dufoq3_0-1711522846996.png

looks totally different compared to this one:

 

dufoq3_2-1711522895989.png

So how your data looks like?

 

This query is connected to your Google Drive excel worksheet. I've filtered top 1000 rows to make it faster (it loads 10 519 rows in result). As you can see, it works great. Regarding your issue: There is a problem that you have stored both "times" in Time1 column. Provide data with this issue and I can try to help you.

let
    Source = Excel.Workbook(Web.Contents("https://drive.google.com/uc?export=download&id=1htdH3Pd9GRSPRb5xuLJTh1CA7OhbKasH")),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    RenamedColumns = Table.RenameColumns(Sheet1_Sheet,{{"Column1", "Postal City Country"}, {"Column2", "Collect Point"}, {"Column3", "Date Time"}}),
    #"!!!_FilteredTOP1000Rows_!!!" = Table.FirstN(RenamedColumns, 1000),
    AddedIndex = Table.AddIndexColumn(#"!!!_FilteredTOP1000Rows_!!!", "Index", 0, 1, Int64.Type),
    Ad_DateTimeSplit = Table.AddColumn(AddedIndex, "DateTimeSplit", each List.Select(List.Transform(Text.Split([Date Time], "#(lf)"), (x)=> Text.Remove(x, {" ", "#(cr)"})), (y)=> Text.Length(y) > 1), type list),
    ExpandedDateTimeSplit = Table.ExpandListColumn(Ad_DateTimeSplit, "DateTimeSplit"),
    SplitColumnByDelimiter = Table.SplitColumn(ExpandedDateTimeSplit, "DateTimeSplit", Splitter.SplitTextByEachDelimiter({":"}, QuoteStyle.Csv, false), {"Date", "Time"}),
    RemovedColumns = Table.RemoveColumns(SplitColumnByDelimiter,{"Date Time"}),
    GroupedRows = Table.Group(RemovedColumns, {"Index", "Date"}, {{"All", each Table.AddIndexColumn(_, "ID", 1, 1, Int64.Type), type table}}),
    CombinedAll = Table.Combine(GroupedRows[All]),
    Transformed = Table.TransformColumns(CombinedAll,
        { {"Collect Point", each Text.Combine(Text.Split(Text.Remove(_, {",", "#(cr)"}), "#(lf)"), ", "), type text},
          {"ID", each "Time" & Text.From(_, "sk-SK"), type text} })
in
    Transformed

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Hi Dufo,

Final output is time1 and time 2 which is not working for friday,

 

bhaskarpbi999_0-1711557785683.png

 

 

I'm sorry but I give up. I've asked for data with this issue, but you either not read what i write or you're kidding...

I will not response to this thread anymore. I recommend you to creat new one with sample data covering your issue in usable format and expected result based on sample data.


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Attached source data. google drive link

https://docs.google.com/spreadsheets/d/12PD4hCdT0pEacfRP_oUYUuYeEnZVmkt2/edit?usp=drive_link&ouid=10... 

 

Only for friday the ID is showing 1 and 1 instead of 1 and 2.

 

 

 

 

 

https://docs.google.com/spreadsheets/d/1kMgzehh1inILMJ7VA-EuHR36vsv53JcO/edit?usp=sharing&ouid=10732... 

 

Only for friday the ID is showing 1 and 1 instead of 1 and 2.

 

I see that there are some discrepancies (see Check column), but I don't see your source data, so I can't help you...

 

let
    Source = Excel.Workbook(Web.Contents("https://drive.google.com/uc?export=download&id=1kMgzehh1inILMJ7VA-EuHR36vsv53JcO")),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    PromotedHeaders = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    GroupedRows = Table.Group(PromotedHeaders, {"Index", "Date"}, {{"All", each Table.AddIndexColumn(_, "ID dufoq3", 1, 1, Int64.Type), type table}}),
    CombinedAll = Table.Combine(GroupedRows[All]),
    Ad_Check = Table.AddColumn(CombinedAll, "Check", each [ID]=[ID dufoq3], type logical)
in
    Ad_Check

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Hi Dufo,

 

Thanks for your support.

i will attach the source data sheet with hours splitted for each weekday so that you can help.

Hi Dufo,

 

Good morning. Sorry to interuppt you.

I have the hours split for all weekdays but only for friday it is showing time1 and time where it should be time1 ana time2.

 

bhaskarpbi999_0-1711475524091.png

Due to which in the output power pivot it is showing time1 and time2 in sigle column for friday.

 

bhaskarpbi999_1-1711475701447.png

 

Will attaxch source file and code

 

let
Source = DHL,
#"Removed Other Columns" = Table.SelectColumns(Source,{"Index", "DateTime"}),
#"Trimmed Text" = Table.TransformColumns(#"Removed Other Columns",{{"DateTime", Text.Trim, type text}}),
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Trimmed Text", {{"DateTime", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "DateTime"),
#"Cleaned Text" = Table.TransformColumns(#"Split Column by Delimiter",{{"DateTime", Text.Clean, type text}}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Cleaned Text", "DateTime", Splitter.SplitTextByEachDelimiter({":"}, QuoteStyle.None, false), {"Date", "Time"}),
GroupedRows = Table.Group(#"Split Column by Delimiter1", {"Index", "Date"}, {{"All", each Table.AddIndexColumn(_, "ID", 1, 1, type text), type table}}),
CombinedAll = Table.Combine(GroupedRows[All]),
AddedPrefix = Table.TransformColumns(CombinedAll, {{"ID", each "Time" & Text.From(_), type text}})
in
AddedPrefix

Hi Dufo,

 

I have the source like below and need ouput with custom column ID with Time1 and Time2 for each weekday.

Source Data

 

bhaskarpbi999_0-1709715501444.png

 

Expected output with ID column highlighted in yellow which is final solution.

 

bhaskarpbi999_1-1709715550477.png

 

 

Regards,

Bhaskar

 

So you've marked your answer as solution 😄 Great...


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

dufoq3_0-1709722533558.png

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("zZXJCsIwEIZfZeg5atKKbb2JCxZcilY8VA8BFyJpKq2+v3VARIxHyVwmmZDl48vhz3NPiCCMPeYNy+qmLkrrsob1VSrTrI2mM0gHs/lyMV4z2FU7M+zAqJJnWSgjawYiYpCWug2JObQhlboozbGGJMHNr6PY4Ds483kARXO7uWv9HoS3Zy5p5sum8LDPeYtHTSWDFD+RRJcQkughEhFL2YacJUSiZWk7JmcJkWhZyqbkLCESLUuTFTlLiPTDUvTsMlmpk/w3TRD7oSXbfJcolhRxz/P5VU55LPnhnoeQH0tyuOch5MeSGe55CPmxpIV7ni8/+wc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Postal = _t, #"City Country" = _t, #"Collect Point" = _t, Date = _t, Time = _t, Index = _t]),
    FilteredRows = Table.SelectRows(Source, each ([Date] <> "null" and [Date] <> null)),
    GroupedRows = Table.Group(FilteredRows, {"Collect Point", "Date"}, {{"All", each Table.AddIndexColumn(_, "ID", 1, 1, type text), type table}}),
    CombinedAll = Table.Combine(GroupedRows[All]),
    AddedPrefix = Table.TransformColumns(CombinedAll, {{"ID", each "Time" & Text.From(_), type text}})
in
    AddedPrefix

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Hi Dufo,

 

Thanks for your support.I am extremely sorry for inconvenience. created new post.

Helpful resources

Announcements
June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

May 2025 Monthly Update

Fabric Community Update - May 2025

Find out what's new and trending in the Fabric community.